CODEWITHSUNDEEP
mysqlsqlaggregate
Mihaela Costea
Mihaela Costea

Reputation: 99

MySQL query that generates Months as columns

I am trying to generate a report in MySQL of monthly revenues per source(provider) and what i want is to see the months as columns and the revenues as rows.

I have written this query but i don\t know what to write to get the months as columns:

SELECT  T1.provider_id, T2.ad_provider, MONTH(date) AS MONTH, round(SUM(T1.revenue),2) as RevenueTotal 
FROM revenue_metrics as T1
LEFT JOIN ad_provider as T2 ON T1.provider_id=T2.id
GROUP BY T1.provider_id, MONTH
ORDER BY MONTH, `RevenueTotal`  DESC

Upvotes: 0

Views: 3006

Answers (2)

Chirag Suthar
Chirag Suthar

Reputation: 21

if You Need Month in Numeric Formate Then 

SELECT MONTH('2008-02-03');
2

if You Need Month in String Formate then

SELECT MONTHNAME('2008-02-03');
'February'

Upvotes: 1

trincot
trincot

Reputation: 350147

You could do it with CASE ... WHEN:

SELECT    T1.provider_id, 
          T2.ad_provider, 
          round(SUM(CASE MONTH(date) WHEN  1 THEN T1.revenue END), 2) AS January,
          round(SUM(CASE MONTH(date) WHEN  2 THEN T1.revenue END), 2) AS February,
          round(SUM(CASE MONTH(date) WHEN  3 THEN T1.revenue END), 2) AS March,
          round(SUM(CASE MONTH(date) WHEN  4 THEN T1.revenue END), 2) AS April,
          round(SUM(CASE MONTH(date) WHEN  5 THEN T1.revenue END), 2) AS May,
          round(SUM(CASE MONTH(date) WHEN  6 THEN T1.revenue END), 2) AS June,
          round(SUM(CASE MONTH(date) WHEN  7 THEN T1.revenue END), 2) AS July,
          round(SUM(CASE MONTH(date) WHEN  8 THEN T1.revenue END), 2) AS August,
          round(SUM(CASE MONTH(date) WHEN  9 THEN T1.revenue END), 2) AS September,
          round(SUM(CASE MONTH(date) WHEN 10 THEN T1.revenue END), 2) AS October,
          round(SUM(CASE MONTH(date) WHEN 11 THEN T1.revenue END), 2) AS November,
          round(SUM(CASE MONTH(date) WHEN 12 THEN T1.revenue END), 2) AS December
FROM      revenue_metrics as T1
LEFT JOIN ad_provider as T2 ON T1.provider_id=T2.id
GROUP BY  T1.provider_id

Note however, that now it no longer makes sense to sort by monthly revenue, since the competing revenues are in different columns now.

Upvotes: 4

Related Questions