MistF
Reputation: 33
Having issue with MySQL
I am having an MySQL issue and here is the query:
SELECT JAN.Sales_Phase, JAN.January, FEB.Febuary, MAR.March, APR.April, MAY.May, JUN.June, JUL.July, AUG.August, SEP.September, OCT.October, NOV.November,
DEC.December
FROM (SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS January
FROM MasterTable_TM
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '1')
GROUP BY Sales_Phase) AS JAN LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS Febuary
FROM MasterTable_TM AS MasterTable_TM_1
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '2')
GROUP BY Sales_Phase) AS FEB ON FEB.Sales_Phase = JAN.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS March
FROM MasterTable_TM AS MasterTable_TM_2
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '3')
GROUP BY Sales_Phase) AS MAR ON MAR.Sales_Phase = FEB.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS April
FROM MasterTable_TM AS MasterTable_TM_3
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '4')
GROUP BY Sales_Phase) AS APR ON APR.Sales_Phase = MAR.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS May
FROM MasterTable_TM AS MasterTable_TM_4
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '5')
GROUP BY Sales_Phase) AS MAY ON MAY.Sales_Phase = APR.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS June
FROM MasterTable_TM AS MasterTable_TM_5
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '6')
GROUP BY Sales_Phase) AS JUN ON JUN.Sales_Phase = MAY.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS July
FROM MasterTable_TM AS MasterTable_TM_6
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '7')
GROUP BY Sales_Phase) AS JUL ON JUL.Sales_Phase = JUN.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS August
FROM MasterTable_TM AS MasterTable_TM_7
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '8')
GROUP BY Sales_Phase) AS AUG ON AUG.Sales_Phase = JUL.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS September
FROM MasterTable_TM AS MasterTable_TM_8
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '9')
GROUP BY Sales_Phase) AS SEP ON SEP.Sales_Phase = AUG.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS October
FROM MasterTable_TM AS MasterTable_TM_9
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '10')
GROUP BY Sales_Phase) AS OCT ON OCT.Sales_Phase = SEP.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS November
FROM MasterTable_TM AS MasterTable_TM_10
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '11')
GROUP BY Sales_Phase) AS NOV ON NOV.Sales_Phase = OCT.Sales_Phase LEFT OUTER JOIN
(SELECT Sales_Phase, COALESCE (SUM(PO_Amount_USD), 0) AS December
FROM MasterTable_TM AS MasterTable_TM_11
WHERE (Sales_Phase = 'Credits Card') OR
(Sales_Phase = 'PO Received') AND (PO_Month = '12')
GROUP BY Sales_Phase) AS DEC ON DEC.Sales_Phase = NOV.Sales_Phase
There is data record inside September and October but for the other month is not having data at all. I tried to run this query but zero result is return. If I remove the other month and leaving only September and October on the query, there is result giving back to me.
How can it be done with showing the other month even if it is on null? The COALESCE () function is used but it's like it is not functioning.
Upvotes: 1
Views: 58
Answers (1)
Jaugar Chang
Reputation: 3196
You really don't need so much join to implement the pivot, just group by and case like this:
SELECT Sales_Phase
, SUM(case PO_Month when 1 then PO_Amount_USD else 0 end) AS January
, SUM(case PO_Month when 2 then PO_Amount_USD else 0 end) AS Febuary
, SUM(case PO_Month when 3 then PO_Amount_USD else 0 end) AS March
, SUM(case PO_Month when 4 then PO_Amount_USD else 0 end) AS April
, SUM(case PO_Month when 5 then PO_Amount_USD else 0 end) AS May
, SUM(case PO_Month when 6 then PO_Amount_USD else 0 end) AS June
, SUM(case PO_Month when 7 then PO_Amount_USD else 0 end) AS July
, SUM(case PO_Month when 8 then PO_Amount_USD else 0 end) AS August
, SUM(case PO_Month when 9 then PO_Amount_USD else 0 end) AS September
, SUM(case PO_Month when 10 then PO_Amount_USD else 0 end) AS October
, SUM(case PO_Month when 11 then PO_Amount_USD else 0 end) AS November
, SUM(case PO_Month when 12 then PO_Amount_USD else 0 end) AS December
FROM MasterTable_TM
WHERE (Sales_Phase = 'Credits Card')
OR (Sales_Phase = 'PO Received')
AND (PO_Month between '1' and '12') -- these conditions do really make no sense, but leave it as the original sample code
GROUP BY Sales_Phase
You have two problems in your query:
- The first sub query will return nothing if no data in January, so the other left joins will return nothing obviously.
(Sales_Phase = 'Credits Card') OR (Sales_Phase = 'PO Received') AND (PO_Month = '1')was so confusing, if Sales_Phase = 'Credits Card' you want return data from all month? The priority ofANDis higer thanOR
Upvotes: 1