Canteen Items Sold on Each Day — SQL Easy Problem

Problem statement

The canteen manager wants a one-line summary of each trading day. For every date on which anything was sold, return:

• sale_date;
• item_count — the number of different items sold that day;
• items — those different items' names in alphabetical order, joined by commas with no spaces (Chai,Samosa).

An item sold several times on one day is counted and listed once. Order the rows by sale_date ascending.

Tables

Table: CanteenSale

Primary key: sale_id.

One row per item sold over the counter. The same item is always spelled the same way.

Examples

Example 1

CanteenSale

Output

How to solve Canteen Items Sold on Each Day

Group the sales by day, then describe each group in two ways.

COUNT(DISTINCT item) counts the different items — a dish sold five times on one day counts once.

GROUP_CONCAT is MySQL's aggregate that joins the values of a group into one string, and its full form has every option this problem needs: GROUP_CONCAT(DISTINCT item ORDER BY item SEPARATOR ','). DISTINCT drops repeated items, ORDER BY item sorts the names alphabetically inside the string, and the separator is a comma with no space (which is also MySQL's default). Without the inner ORDER BY the items come out in whatever order the rows happen to be read, and the answer can change when the table's storage order does.

The outer ORDER BY sale_date then sorts the result rows themselves — a different job from the ORDER BY inside GROUP_CONCAT. Dates stored as YYYY-MM-DD sort chronologically.

An alternative removes duplicates first: SELECT DISTINCT sale_date, item in a derived table, then group that by date with a plain COUNT(*) and GROUP_CONCAT(item ORDER BY item). Both read the table once and sort within each day, so the cost is about O(n log n). One practical note: MySQL truncates a GROUP_CONCAT result at group_concat_max_len (1,024 bytes by default), which matters only for very long lists.

Reference solution (MySQL)

SELECT sale_date,
       COUNT(DISTINCT item) AS item_count,
       GROUP_CONCAT(DISTINCT item ORDER BY item SEPARATOR ',') AS items
FROM CanteenSale
GROUP BY sale_date
ORDER BY sale_date

Another way

SELECT sale_date, COUNT(*) AS item_count, GROUP_CONCAT(item ORDER BY item SEPARATOR ',') AS items
FROM (SELECT DISTINCT sale_date, item FROM CanteenSale) d
GROUP BY sale_date
ORDER BY sale_date

Another way

SELECT sale_date, COUNT(DISTINCT item) AS item_count, GROUP_CONCAT(DISTINCT item ORDER BY item) AS items FROM CanteenSale GROUP BY sale_date ORDER BY sale_date ASC