데이터베이스 질문과 답변
with raw_data as (
select fruit, cast(sum(fruit_buy) as unsigned) as fruit_buy, cast(count(fruit) as unsigned) as fruit_count from example_table
where date = '202210'
group by fruit
order by fruit_buy desc
limit 5
)
select fruit, fruit_buy, fruit_count
from raw_data
group by fruit with rollup
| fruit | fruit_buy | fruit_count | |
| 1 | 포도 | 4000 | 400 |
| 2 | 사과 | 5000 | 300 |
| 3 | 수박 | 2000 | 200 |
| 4 | 참외 | 1000 | 500 |
| 5 | 딸기 | 3000 | 100 |
| [NULL] | 3000 | 100 | |
위의 쿼리를 통해 fruit 그룹별로 최대 5개 이하로 정보를 뽑아서 맨 아래에 Total 옆에 위의 5개의 합을 구하고, fruit_buy, fruit_count가 큰 순서대로 나오게 하고 싶은데, 위처럼 결과가 나오는데, 혹시 어떻게 해야 할까요?
도와주세요.... (아래처럼 나오게 하고 싶습니다...)
| fruit | fruit_buy | fruit_count | |
| 1 | 사과 | 5000 | 300 |
| 2 | 포도 | 4000 | 400 |
| 3 | 딸기 | 3000 | 100 |
| 4 | 수박 | 2000 | 200 |
| 5 | 참외 | 1000 | 500 |
| Total | 15000 | 1500 | |
by 마농
[2022.11.07 13:06:42]
SELECT IFNULL(fruit, 'Total') fruit
, fruit_buy
, fruit_cnt
FROM (SELECT fruit
, SUM(fruit_buy) fruit_buy
, SUM(fruit_cnt) fruit_cnt
FROM (SELECT fruit
, SUM(fruit_buy) fruit_buy
, COUNT(*) fruit_cnt
FROM example_table
WHERE date = '202210'
GROUP BY fruit
ORDER BY fruit_buy DESC
LIMIT 5
) a
GROUP BY fruit
WITH ROLLUP
) a
ORDER BY CASE WHEN fruit IS NULL THEN 0 ELSE fruit_buy END DESC
;
by 메밀묵도토리묵
[2022.11.07 13:32:17]
감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.