CODEWITHSUNDEEP
mysqlselectjoinmergeunion
ToddN
ToddN

Reputation: 2961

MySQL Merge Two SELECT Queries With CASE On One Table

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

Upvotes: 1

Views: 272

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions