MySQL: SELECT with SUM() and COUNT() as new columns

Question

What is the correct SELECT statement to produce this desired output?

+------------+----------+-------------+-----------------+
| QtyOfParts | FamilyID | NumInFamily | TotalQtyOfParts |
+------------+----------+-------------+-----------------+
 28 1 3 79
 32 1 3 79
 19 1 3 79
 28 2 2 66
 38 2 2 66
 46 3 4 151
 30 3 4 151
 51 3 4 151
 24 3 4 151
...more rows...

'QtyOfParts' and 'FamilyID' are existing columns in the table.

'NumInFamily' is a calculated column for display/report; its value is the COUNT() of each unique FamilyID. E.g., FamilyID = 1 has 3 in its family; FamilyID = 2 has 2 in its family; FamilyID = 3 has 4 in its family.

'TotalQtyOfParts' is a calculated column for display/report; its values is the SUM() of the QtyOfParts for each FamilyID. E.g., FamilyID = 1 has 28 +たす 32 +たす 19 =わ 79 parts; FamilyID = 2 has 19 + 28 = 66 parts.

Using 'SELECT ... COUNT(FamilyID) ... GROUP BY FamilyID' fails to show each and every row -- instead it shows only one row for each FamilyID. So for example instead of seeing three rows for FamilyID = 1, only one row is shown. (Note: ROLLUP made no difference.)

Using SUM() in the select simply tallied the sum of every single value under QtyOfParts.

CREATE TABLE IF NOT EXISTS `Test`.`NoOps` (
 `ID_NoOps` INT NOT NULL,
 `QtyOfParts` INT(3) NOT NULL,
 `FamilyID` INT(3) NOT NULL,
 PRIMARY KEY (`ID_NoOps`))
ENGINE = InnoDB

• joanolo: It benefit a lot to add as well, some sample input data for your table, and the expected result given this input data.

Answer 1

If we assume that 'QtyOfParts' and 'FamilyID' are existing columns in the table and furthermore, that the other 2 columns are "calculated", then maybe the following query would be helpful (creating some test data first):

create table qf (
 QtyOfParts integer
, FamilyID integer 
);
insert into qf values 
(28, 1), (32, 1), (19, 1), 
(28, 2), (38, 2), (46, 3),
(30, 3), (51, 3), (24, 3);

Thus:

mysql> select * from qf;
+------------+----------+
| QtyOfParts | FamilyID |
+------------+----------+
| 28 | 1 |
| 32 | 1 |
| 19 | 1 |
| 28 | 2 |
| 38 | 2 |
| 46 | 3 |
| 30 | 3 |
| 51 | 3 |
| 24 | 3 |
+------------+----------+
9 rows in set (0.00 sec)

Suggested query:

mysql> select 
 -> t1.qtyofparts
 -> , t1.familyid
 -> , t2.nbr NumInFamily
 -> , t2.sum FullQtyOfParts 
 -> from qf t1 join ( 
 -> select 
 -> familyid
 -> , count(familyid) nbr
 -> , sum(qtyofparts) sum
 -> from qf 
 -> group by familyid ) t2 on t1.familyid = t2.familyid
 -> ;
+------------+----------+-------------+----------------+
| qtyofparts | familyid | NumInFamily | FullQtyOfParts |
+------------+----------+-------------+----------------+
| 28 | 1 | 3 | 79 |
| 32 | 1 | 3 | 79 |
| 19 | 1 | 3 | 79 |
| 28 | 2 | 2 | 66 |
| 38 | 2 | 2 | 66 |
| 46 | 3 | 4 | 151 |
| 30 | 3 | 4 | 151 |
| 51 | 3 | 4 | 151 |
| 24 | 3 | 4 | 151 |
+------------+----------+-------------+----------------+
9 rows in set (0.00 sec)