01/09 회고

SQL쿼리

2중 정렬

SELECT
    SUM(cnt) AS count,
    total.sumCount,
    (CASE 
        WHEN exCase = '1' THEN 'value1'
        WHEN exCase = '2' THEN 'value2'
        ...
        WHEN exCase = '24' THEN 'value24' 
        ELSE '25' 
    END) as category,
    ...
FROM public.table, (SELECT SUM(cnt) AS sumCount FROM public.table) AS total
GROUP BY exCase, total.sumCount
ORDER BY
    CASE 
        WHEN category = 'value24' THEN 1
        WHEN category = 'value25' THEN 1  -- 여기서 '25'는 '상피내암'에 해당하는 코드를 가정
        ELSE 0 
    END,
    count DESC;

만약 SQL Error [42703]: ERROR: column " category" does not exist 에러가 발생한다면 category 컬럼을 매칭시키지 못하는 것이라 아래 Order By의 조건을 수정해주어야 한다.

SELECT
    SUM(cnt) AS count,
    total.sumCount,
    (CASE 
        WHEN exCase = '1' THEN '1'
        WHEN exCase = '2' THEN '2'
        ...
        WHEN exCase = '24' THEN '24' 
        ELSE '25' 
    END) as category,
    ...
FROM public.table, (SELECT SUM(cnt) AS sumCount FROM public.table) AS total
GROUP BY exCase, total.sumCount
ORDER BY
    CASE 
        WHEN exCase = '24' THEN 1
        WHEN exCase = '25' THEN 1  -- 여기서 '25'는 '상피내암'에 해당하는 코드를 가정
        ELSE 0 
    END,
    count DESC;

 

 

합계 row추가하기

group by로 각각의 데이터를 구분지은 select결과와 group by로 전체를 묶은 select결과를 UNION 또는 UNION ALL로 묶어줌

	(SELECT
		(CASE
			WHEN type = '1' THEN '남'
			WHEN type = '2' THEN '여'
		END) AS itemNm,
		SUM(cnt) AS count,
		crit AS category,
		SUM(cnt) AS sumCount
	FROM public.table
	GROUP BY type, crit, stat)
UNION ALL
	(SELECT
		'전체' AS itemNm,
		SUM(cnt) AS count,
		crit AS category,
		SUM(cnt) AS sumCount
	FROM public.table
	GROUP BY crit, stat)
-- UNION ALL 이후에 ORDER BY하여 정렬
ORDER BY category, stat;