1

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
  • It benefit a lot to add as well, some sample input data for your table, and the expected result given this input data. – joanolo Jun 11 '17 at 18:00
3

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.