[LeetCode_SQL] 1484. Group Sold Products By The Date - Oracle & MySQL

Table Activities:

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.

 

Write an SQL query to find for each date the number of different products sold and their names.

The sold products names for each date should be sorted lexicographically.

Return the result table ordered by sell_date.

The query result format is in the following example.

 

Example 1:

Input: 
Activities table:
+------------+------------+
| sell_date  | product     |
+------------+------------+
| 2020-05-30 | Headphone  |
| 2020-06-01 | Pencil     |
| 2020-06-02 | Mask       |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible      |
| 2020-06-02 | Mask       |
| 2020-05-30 | T-Shirt    |
+------------+------------+
Output: 
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
Explanation: 
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.
For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.
For 2020-06-02, the Sold item is (Mask), we just return it.

---

Activities 테이블에서 sell_date 를 기준으로 그룹화 한뒤 product 갯수와 product 데이터에 구분자(,) 를 넣어 출력하기

 

 Oracle  

LISTAGG : 구분자를 지정

WITHIN GROUP (ORDER BY) 이 항상 따라와야함

SELECT TO_CHAR(sell_date,'YYYY-MM-DD') 
AS sell_date,
    COUNT(product) 
AS num_sold,
    LISTAGG(product,',') WITHIN GROUP (ORDER BY product) 
AS products
FROM (
    SELECT DISTINCT sell_date,product
    FROM activities
    )
GROUP BY sell_date 
ORDER BY sell_date;

 

 MySQL 

SEPARATOR : 구분자를 지정 ( Default : ' , ' )

SELECT sell_date,
		COUNT(DISTINCT(product)) AS num_sold, 
		GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;