Using nested queries with COUNT function

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:

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:

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.

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 (like YEAR and MONTH), or use date comparison with the first of the month.

SELECT MealDate, 
       COUNT(CASE WHEN MealType = 1 THEN ID_Student END) AS Breakfasts,
       COUNT(CASE WHEN MealType = 2 THEN ID_Student END) AS Lunches,
       COUNT(CASE WHEN MealType = 3 THEN ID_Student END) AS Dinners
FROM tab
GROUP BY MealDate
WHERE YEAR(MealDate) = 2023 AND MONTH(MealDate) = 5
# WHERE MealDate BETWEEN '2023-05-01' AND '2023-06-01'

Output:

Check the demo here.

You can do it using the conditional aggregation as follows :

select MealDate, COUNT(CASE WHEN MealType = 1 THEN ID_Student END) AS Breakfasts,
                 COUNT(CASE WHEN MealType = 2 THEN ID_Student END) AS Lunches,
                 COUNT(CASE WHEN MealType = 3 THEN ID_Student END) AS Dinners
from mytable
group by MealDate

Demo here