-
Notifications
You must be signed in to change notification settings - Fork 0
/
group_concat
36 lines (24 loc) · 1.01 KB
/
group_concat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# Example.
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.
# Answer.
#https://www.geeksforgeeks.org/mysql-group_concat-function/
#https://fruitdev.tistory.com/16
#GROUP_CONCAT
#- 그룹별로 데이터를 concatenate 해주는 함수
#- distinct, seperator(구분자), order by 조건을 함께 활용 할 수 있다.
SELECT sell_date, COUNT(DISTINCT product) 'num_sold', GROUP_CONCAT(DISTINCT product) 'products'
FROM Activities
GROUP BY sell_date
ORDER BY sell_date ASC