데이터베이스 질문과 답변
SELECT ad_vendor AS ad_vendor,
FORMAT(SUM(ad_price), 0) AS total,
FORMAT(SUM(ad_price)*1.1, 0) AS total2,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2012' THEN ad_price ELSE 0 END), 0) AS aa,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2013' THEN ad_price ELSE 0 END), 0) AS bb,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2014' THEN ad_price ELSE 0 END), 0) AS cc,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2015' THEN ad_price ELSE 0 END), 0) AS dd,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2016' THEN ad_price ELSE 0 END), 0) AS ee,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2017' THEN ad_price ELSE 0 END), 0) AS ff,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2018' THEN ad_price ELSE 0 END), 0) AS gg,
FORMAT(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2019' THEN ad_price ELSE 0 END), 0) AS hh,
GROUP_CONCAT(DISTINCT(ad_charge) SEPARATOR ',') AS CHARGE
FROM tbladlist
WHERE 1 = 1
AND ad_price != ''
GROUP BY ad_vendor WITH ROLLUP
여기에서 가장 하단에 붙어있는 합계를 가장 상단으로 붙이진 못할까요?
ORDER BY 를 쓰니 에러가 납니다.ㅠ
by jkson
[2019.09.10 11:20:45]
mysql도 grouping 함수가 있는 것 같은데
order by grouping(ad_vendor) desc 해보시죠.
by 진짜강동원
[2019.09.10 11:56:17]
MYSQL 버전 때문인지.. 사용할 수 없는 펑션이라 뜨네요 ㅠ
by 마농
[2019.09.10 12:34:32]
1. 날짜조건이 있어야 하지 않나요? -> 그래야 연도별 합계와 전체 합계가 일치할텐데요?
2. format 사용이 굳이 필요한가요?
3. with rollup 시 정렬이 그냥은 안되네요. -> 인라인뷰 이용해 정렬하세요.
SELECT *
FROM (SELECT ad_vendor
, SUM(ad_price) AS total
, SUM(ad_price)*1.1 AS total2
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2012' THEN ad_price END), 0) AS aa
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2013' THEN ad_price END), 0) AS bb
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2014' THEN ad_price END), 0) AS cc
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2015' THEN ad_price END), 0) AS dd
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2016' THEN ad_price END), 0) AS ee
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2017' THEN ad_price END), 0) AS ff
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2018' THEN ad_price END), 0) AS gg
, IFNULL(SUM(CASE DATE_FORMAT(ad_getdate, '%Y') WHEN '2019' THEN ad_price END), 0) AS hh
, GROUP_CONCAT(DISTINCT ad_charge) AS charge
FROM tbladlist
WHERE 1 = 1
AND ad_price != ''
AND ad_getdate >= '2012-01-01'
AND ad_getdate < '2020-01-01'
GROUP BY ad_vendor
WITH ROLLUP
) a
ORDER BY ad_vendor
;
by 진짜강동원
[2019.09.10 15:16:42]
아.. 간단하게 생각 하면 됐네요.. 감사합니다~^^
) 버튼을 클릭하여 작성 하시면 됩니다.