mysql select query with sum and group by from two columns

Question

I have a table named ward

+---------+-----------+
| ward_id | ward_name |
+---------+-----------+
|       3 | Ward 1    |
|       4 | Ward 2    |
+---------+-----------+

+----+----------+----------------+---------+---------+--------------+------------+
| id | username | date_allocated | ward_id | shop_id | food_name    | amount     |
+----+----------+----------------+---------+---------+--------------+------------+
|  1 | STORE1   | 2020-04-06     |       4 |       2 | Beans        | 1000 Bags  |
|  2 | STAMP    | 2020-04-21     |       4 |       2 | Maize Grains | 60 bags    |
|  3 | STORE1   | 2020-04-21     |       4 |       2 | Maize Flour  | 120 bags   |
|  4 | STORE1   | 2020-04-21     |       4 |       2 | Wheat Flour  | 120 bags   |
|  5 | STAMP    | 2020-04-24     |       3 |       2 | Maize Flour  | 50 bales   |
|  6 | STAMP    | 2020-04-25     |       4 |       2 | Maize Flour  | 1000 Bales |
|  7 | STAMP    | 2020-04-25     |       4 |       2 | Wheat Flour  | 150 pkts   |
+----+----------+----------------+---------+---------+--------------+------------+

spent_food

+----+----------+------------+---------+---------+---------+-------------+-----------+
| id | username | date_spent | idno    | ward_id | shop_id | food_name   | amount    |
+----+----------+------------+---------+---------+---------+-------------+-----------+
|  5 | STAMP    | 2020-04-21 | 3000000 |       4 |       2 | Beans       | 50 pkts   |
|  6 | STAMP    | 2020-04-21 | 3000000 |       4 |       2 | Maize Flour | 1000 Bags |
|  7 | STAMP    | 2020-04-24 | 3000000 |       3 |       2 | Wheat Flour | 4 Packets |
|  8 | STAMP    | 2020-04-25 | 3000000 |       3 |       3 | Maize Flour | 50 bales  |
|  9 | STAMP    | 2020-04-25 | 3000000 |       3 |       3 | Wheat Flour | 50 pkts   |
| 10 | STAMP    | 2020-04-25 | 3000000 |       4 |       2 | Wheat Flour | 120 Bales |
+----+----------+------------+---------+---------+---------+-------------+-----------+

I have Created a query

SELECT ward.ward_name, ward.ward_id, 
       food_allocation.food_name, food_allocation.ward_id, spent_food.food_name, 
       sum(food_allocation.tot1) as food,
       sum(spent_food.tot2) as spent,
       sum(food_allocation.tots-spent_food.tots) as Balance
FROM ward
INNER JOIN (
  SELECT ward_id,food_name,
         sum(amount) tot1,
         sum(amount) tots
  FROM food_allocation 
  GROUP BY food_name,ward_id
) food_allocation ON ward.ward_id = food_allocation.ward_id
INNER JOIN (
  SELECT ward_id,food_name,
         sum(amount) tot2,
         sum(amount) tots
  FROM spent_food 
  GROUP BY food_name, ward_id
) spent_food ON food_allocation.ward_id = spent_food.ward_id 
GROUP BY food_allocation.ward_id, food_allocation.food_name 
ORDER BY food_allocation.ward_id;

Am getting this wrong figures below

+-----------+---------+--------------+---------+-------------+------+-------+---------+
| ward_name | ward_id | food_name    | ward_id | food_name   | food | spent | Balance |
+-----------+---------+--------------+---------+-------------+------+-------+---------+
| Ward 1    |       3 | Maize Flour  |       3 | Maize Flour |  100 |   104 |      -4 |
| Ward 2    |       4 | Beans        |       4 | Beans       | 3000 |  1170 |    1830 |
| Ward 2    |       4 | Maize Flour  |       4 | Beans       | 3360 |  1170 |    2190 |
| Ward 2    |       4 | Maize Grains |       4 | Beans       |  180 |  1170 |    -990 |
| Ward 2    |       4 | Wheat Flour  |       4 | Beans       |  810 |  1170 |    -360 |
+-----------+---------+--------------+---------+-------------+------+-------+---------+

My Expected answer is

+-----------+---------+--------------+---------+-------------+------+-------+---------+
| ward_name | ward_id | food_name    | ward_id |     food | spent | Balance |
+-----------+---------+--------------+---------+-------------+------+-------+---------+
| Ward 1    |       3 | Maize Flour  |       3 |       50 |    50 |      0 |
| Ward 2    |       4 | Beans        |       4 |     1000 |    50 |    950 |
| Ward 2    |       4 | Maize Flour  |       4 |     1120 |  1000 |    120 |
| Ward 2    |       4 | Maize Grains |       4 |       60 |     0 |     60 |
| Ward 2    |       4 | Wheat Flour  |       4 |      270 |   120 |    150 |
+-----------+---------+--------------+---------+-------------+------+-------+---------+

forpas · Accepted Answer · 2020-04-25 16:30:45Z

You must turn your joins to LEFT joins and also join the queries on the correct columns.
Also since you are aggregating inside each subquery you don't need the final aggregation, so I removed the last GROUP BY:

SELECT w.ward_name, 
       w.ward_id, 
       fa.food_name,
       coalesce(fa.tot1,0) as food,
       coalesce(sf.tot2,0) as spent,
       coalesce(fa.tots,0)-coalesce(sf.tots,0) as Balance
FROM ward w
LEFT JOIN (
  SELECT ward_id,food_name,
         sum(amount) tot1,
         sum(amount) tots
  FROM food_allocation 
  GROUP BY food_name,ward_id
) fa  ON w.ward_id = fa.ward_id
LEFT JOIN (
  SELECT ward_id,food_name,
         sum(amount) tot2,
         sum(amount) tots
  FROM spent_food 
  GROUP BY food_name, ward_id
) sf ON fa.ward_id = sf.ward_id  AND fa.food_name = sf.food_name
ORDER BY fa.ward_id, fa.food_name;

See the demo.
Results:

> ward_name | ward_id | food_name    | food | spent | Balance
> :-------- | ------: | :----------- | ---: | ----: | ------:
> Ward 1    |       3 | Maize Flour  |   50 |    50 |       0
> Ward 2    |       4 | Beans        | 1000 |    50 |     950
> Ward 2    |       4 | Maize Flour  | 1120 |  1000 |     120
> Ward 2    |       4 | Maize Grains |   60 |     0 |      60
> Ward 2    |       4 | Wheat Flour  |  270 |   120 |     150

nbk · Accepted Answer · 2020-04-25 16:12:42Z

When you yoin your last table Sped_food you must join them at the right place that is ward_id and food_name.

FYI I shortened the names of the tables, so that there i less text and it is so much better to read

SELECT w.ward_name, w.ward_id, 
       f.food_name
       , MAX(f.ward_id)
       , MAX(s.food_name), 
       sum(f.tot1) as food,
       sum(s.tot2) as spent,
       sum(f.tots-s.tots) as Balance
FROM ward w
INNER JOIN (
  SELECT ward_id,food_name,
         sum(amount) tot1,
         sum(amount) tots
  FROM food_allocation 
  GROUP BY food_name,ward_id
) f  ON w.ward_id = f.ward_id
INNER JOIN (
  SELECT ward_id,food_name,
         sum(amount) tot2,
         sum(amount) tots
  FROM spent_food 
  GROUP BY food_name, ward_id
) s ON f.ward_id = s.ward_id  AND f.food_name = s.food_name
GROUP BY w.ward_name, w.ward_id, f.food_name 
ORDER BY f.ward_id;

Collectives™ on Stack Overflow

mysql select query with sum and group by from two columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related