How to Write a SQL Statement that Sums All Records of Different Items in Each Group as “Total Balance”

Problem: In “Stock Card” table, it records the stock movement, “In” and “Out”  of each item (ITEM ID).  There are different items that belongs to same group (GROUP).  And there are many groups (GROUP).

 

STOCK CARD TABLE
GROUP ITEM ID IN OUT
EKONOR EKONOR-1 20
EKONOR EKONOR-1 10
EKONOR EKONOR-2 40
EKONOR EKONOR-2 10
EKONOR EKONOR-2 20
SAGA SAGA-1 10
SAGA SAGA-1 5
SAGA SAGA-1 5
SAGA SAGA-2 10
SAGA SAGA-2 10
SAGA SAGA-2 20

How to get the sum (TOTAL BALANCE) of each group as below?

GROUP TOTAL RECORDS TOTAL BALANCE
EKONOR 5 20
SAGA 6 10

 

Solution: using this SQL statement

SELECT `Group`,
(SELECT COUNT(Stock_Description.`Group`) FROM Stock_Description WHERE Stock_Description.`Group` = sd.`Group`) As `TOTAL RECORDS`,
(SELECT SUM(COALESCE(a.`IN`,0) – COALESCE(a.`OUT`,0)) FROM Stock_Card a WHERE a.Item_ID LIKE CONCAT(sd.`Group, ‘%’) AND a.Balance IS NOT NULL) AS `TOTAL BALANCE` FROM Stock_Description sd GROUP BY `Group`;

 

SELECT SUM(COALESCE(a.`IN`,0) – COALESCE(a.`OUT`,0)) FROM Stock_Card a WHERE a.Item_ID LIKE CONCAT(sd.`Group`, ‘%’) AND a.Balance IS NOT NULL) AS Total_Balance

the above SQL statement will display  a “TOTAL BALANCE” of each “GROUP” by:-

  • summing all “In” records of “EKONOR” minus the sum of all “Out” of EKONOR”
    • (20 + 40) – (10 + 10 + 20)
    • = 20
  • summing all “In” records of “SAGA” minus the sum of all “Out” of  “SAGA”
    • (10 + 5 + 10 + 10) – (5 + 20)
    • = 10

 

SELECT COUNT(Stock_Description.`Group`) FROM Stock_Description WHERE Stock_Description.`Group` = sd.`Group`) As `TOTAL RECORDS`

the above SQL statement will display  a “TOTAL RECORDS” of each “GROUP” by:-

  • summing all records in “EKONOR” group
  • summing all records in “SAGA” group

Source:-