MySQL: SELECT with SUM() and COUNT() as new columns 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
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&gt; 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&gt; select 
    -&gt;   t1.qtyofparts
    -&gt; , t1.familyid
    -&gt; , t2.nbr NumInFamily
    -&gt; , t2.sum FullQtyOfParts 
    -&gt; from qf t1 join ( 
    -&gt;     select 
    -&gt;       familyid
    -&gt;     , count(familyid) nbr
    -&gt;     , sum(qtyofparts) sum
    -&gt;     from qf 
    -&gt;     group by familyid ) t2 on t1.familyid = t2.familyid
    -&gt; ;
+------------+----------+-------------+----------------+
| 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)