I want to merge two queries into one full query. I would need the two queries to group on one aggregate field (MFG). I would assume you use a JOIN but it is on the same table and with a CASE so I'm not sure how that could even work.
1ST QUERY (2015)
SELECT
CASE
WHEN ITEM LIKE '%1%' THEN 'MFG1'
WHEN ITEM LIKE '%2%' THEN 'MFG2'
WHEN ITEM LIKE '%3%' THEN 'MFG3'
ELSE ''
END AS MFG,
SUM(COST) AS Cost2015
FROM
table
WHERE
TRANS_DATE BETWEEN '2015-12-01' AND '2015-12-31'
GROUP BY MFG
2ND QUERY (2014)
SELECT
CASE
WHEN ITEM LIKE '%1%' THEN 'MFG1'
WHEN ITEM LIKE '%2%' THEN 'MFG2'
WHEN ITEM LIKE '%3%' THEN 'MFG3'
ELSE ''
END AS MFG,
SUM(COST) AS Cost2014
FROM
table
WHERE
TRANS_DATE BETWEEN '2014-12-01' AND '2014-12-31'
GROUP BY MFG
DESIRED RESULT
MFG | Cost2015 | Cost2014
MFG1 | 1500 | 0
MFG2 | 1000 | 1200
MFG3 | 0 | 3600
| 100 | 15
You can use CASE in SUM()
SELECT
CASE
WHEN ITEM LIKE '%1%' THEN 'MFG1'
WHEN ITEM LIKE '%2%' THEN 'MFG2'
WHEN ITEM LIKE '%3%' THEN 'MFG3'
ELSE ''
END AS MFG,
SUM(CASE WHEN TRANS_DATE BETWEEN '2014-12-01' AND '2014-12-31' THEN COST ELSE 0 END) AS Cost2014,
SUM(CASE WHEN TRANS_DATE BETWEEN '2015-12-01' AND '2015-12-31' THEN COST ELSE 0 END) AS Cost2015
FROM
TABLE
WHERE
TRANS_DATE BETWEEN '2014-12-01' AND '2015-12-31'
GROUP BY MFG
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다