I am using an SQL table to keep track of the number of meals given to students every day. The table is structured as follows:
ID_Student | MealDate | MealType |
---|---|---|
1 | 2023-05-21 | 1 |
2 | 2023-05-21 | 1 |
1 | 2023-05-21 | 2 |
2 | 2023-05-21 | 2 |
1 | 2023-05-22 | 1 |
1 | 2023-05-23 | 3 |
The MealType columns specifies the type of meal:
- 1 = Breakfast
- 2 = Lunch
- 3 = Dinner
Individual students can therefore have up to 3 records for each day.
I am now trying to create an invoice using the data in the table "Meals". For that, I need an SQL query that generates the total number of breakfasts, lunches and dinners for each day in a given month. Based on the table shown above it should look something like this:
MealDate | Breakfasts | Lunches | Dinners |
---|---|---|---|
2023-05-21 | 2 | 2 | 0 |
2023-05-22 | 1 | 0 | 0 |
2023-05-23 | 0 | 0 | 1 |
I assume nested queries are the best approach here, so this is how far I’ve got:
SELECT MealDate, BreakfastCount, LunchCount, DinnerCount FROM
(
SELECT MealDate, LunchCount, DinnerCount, COUNT(*) AS "BreakfastCount" FROM
(
SELECT MealDate, MealType, DinnerCount, COUNT(*) AS "LunchCount" FROM
(
SELECT MealDate, MealType, COUNT(*) AS "DinnerCount" FROM Meals
WHERE MealType=3 AND MealDate LIKE '2023-05-%'
GROUP BY MealDate
) Dinners
WHERE MealType=2 AND MealDate LIKE '2023-05-%'
GROUP BY MealDate
) Lunches
WHERE MealType=1 AND MealDate LIKE '2023-05-%'
GROUP BY MealDate
) Breakfasts
GROUP BY MealDate
Problem is, the result is always empty. How can I make this work? The individual queries by themselves produce the expected results, but the nesting breaks it. I have a suspicion it has to do with the MealTypes in the WHERE clauses, but I don’t know how else to restrict the COUNT(*) function to the specific meal type.
2
Answers
Your approach is good, although shall be tweaked a little to make it working. First move your conditions from the
WHERE
clauses to the aggregate functions (conditional aggregation). Then as you notice, you’re grouping always on the same field: you can pull all your count aggregates in a single query.If you want to filter your records to be on May 2023, instead of
LIKE
, it would be better to use date functions (likeYEAR
andMONTH
), or use date comparison with the first of the month.Output:
Check the demo here.
You can do it using the conditional aggregation as follows :
Demo here