MySQL Cost by Category throughout time

Question

I have a table that looks like this:

Site | Category | Cost | Month |
A    | Hardware | 10   | 1     |
A    | Software | 30   | 1     |
B    | Software | 15   | 1     |
C    | Labor    | 5    | 2     |
...

I need to output this:

Site | Category |  1   |  2  | ...
A    | Hardware | 10   |  0  |
A    | Software | 30   |  0  |
B    | Software | 15   |  0  |
C    | Labor    |  0   |  5  |

What would be the best way to use the records under the Month attribute as column headers and allocate the Cost records under their corresponding Month columns?

Do you need a pure SQL response? Getting a variable number of columns in pure SQL is not feasible. You have to resort to another language or use stored procedures that create temporary tables. BUT if you know in advance the number of columns (in this case exactly 12?) then it's feasible — Jacques Amar, Aug 31 '17 at 00:35
Yes, I need a pure MySQL response. Yes! I need exactly 12 columns. How do you suggest doing it? — Christián De Achával, Aug 31 '17 at 00:41
Seriously consider handling issues of data display in application code — Strawberry, Aug 31 '17 at 06:31

Jacques Amar · Accepted Answer · 2017-08-31T01:07:27.453

1

SELECT Site, Category, 
   IF(Month=1,Cost,0) as M1,
   IF(Month=2,Cost,0) as M2,
   IF(Month=3,Cost,0) as M3,
   IF(Month=4,Cost,0) as M4,
   IF(Month=5,Cost,0) as M5,
   IF(Month=6,Cost,0) as M6,
   IF(Month=7,Cost,0) as M7,
   IF(Month=8,Cost,0) as M8,
   IF(Month=9,Cost,0) as M9,
   IF(Month=10,Cost,0) as M10,
   IF(Month=11,Cost,0) as M11,
   IF(Month=12,Cost,0) as M12
FROM tablename

Will give a line by line. You can add a GROUP BY if you want one line per Category

SELECT Site, Category, 
   SUM(IF(Month=1,Cost,0)) as M1,
   SUM(IF(Month=2,Cost,0)) as M2,
   SUM(IF(Month=3,Cost,0)) as M3,
   SUM(IF(Month=4,Cost,0)) as M4,
   SUM(IF(Month=5,Cost,0)) as M5,
   SUM(IF(Month=6,Cost,0)) as M6,
   SUM(IF(Month=7,Cost,0)) as M7,
   SUM(IF(Month=8,Cost,0)) as M8,
   SUM(IF(Month=9,Cost,0)) as M9,
   SUM(IF(Month=10,Cost,0)) as M10,
   SUM(IF(Month=11,Cost,0)) as M11,
   SUM(IF(Month=12,Cost,0)) as M12
FROM tablename
GROUP BY Site, Category

Will keep Site, but sum up Category.

Also, if you don't like the Mx name for the field, you can try as '1' .. '2' etc.I think it should work

edited Aug 31 '17 at 01:07

answered Aug 31 '17 at 00:42

Jacques Amar

1,803
1
10
12

This is Amazing!! Thank you very much. One last question regarding this problem. I have 20 sites and 4 categories. I would like to rollup all cost per category but see all the sites as well. Is there a way to group by category while keeping the sites untouched? – Christián De Achával Aug 31 '17 at 00:52
See example, you can play with the GROUP BY – Jacques Amar Aug 31 '17 at 01:07
How do I fix for a data base with several years? I tried using the where clause at the end but the numbers come too big, I think the sum() function is executing the data per month without considering the where clause at the end. Is it possible to use a double if function? something like: sum(if(year(invoice_date)=2017, if(month(invoice_date)=1, Cost, 0), 0) as Jan ? I know excel allows that but I'm trying in MySQL Workbench and it claims a syntax error – Christián De Achával Sep 15 '17 at 18:26
*If* you have a field called `theYear` change the `GROUP BY Site, Category, theYear` – Jacques Amar Sep 15 '17 at 23:33
Is there a way to extend the month columns horizontally year over year? Such as Jan 16 | Feb 16 | Mar 16...| Dec 16 | Jan 17 | Feb 17 Take into account that the "Month=1" clause is in reality" Month(Invoice_Date)=1 and I'm using a Where Year(Invoice_Date) = 2016 to bring the 2016 data by month. I'm trying to get to the point where 1 query executes all the cost data year over year by month for 1 Site. Thank you so much for all your help, this is amazing – Christián De Achával Sep 27 '17 at 18:52
MySQL works with fixed columns. It's always feasible but harder and **ridiculously inflexible**. Use MySQL to store and retrieve, use another language to display and make pretty. – Jacques Amar Sep 27 '17 at 20:24
But we are so close to the optimal result! I can't believe there is no way to lay out the monthly cost for the lifetime of the project. Our current solution only provides the monthly cost for a defined year in the "where" clause. If I don't use the where clause then the months will provide incorrect numbers because they will be adding all month costs in one column (ex: Jan 2016 and Jan 2017 will be summed under Jan). I would like to understand why is it impossible to use a "if" function to have MySQL retrieve the sum(Month=1 & Year=2016). It doesn't seem to work and it's frustrating :( – Christián De Achával Sep 27 '17 at 20:59
1

Show me your exact script. But you would need more columns - not just `M1`, but `M1_2016` ... `M12_2016`, `M1_2017`.. `M12_2017` etc. Also, just in case you didn't know , it's `AND` and not `&` (`Month=1 AND Year=2016`) – Jacques Amar Sep 27 '17 at 21:55
Hi again! I have a cost attribute in another table that I need to merge into this table. Is possible to make a join on Site, Year, and Month to somehow embed the new cost into the old cost so I can have the output as a monthly cost (cost A + cost B)? – Christián De Achával Nov 03 '17 at 23:36
@ChristiánDeAchával, the syntax is `LEFT JOIN C ON C.Site=tablename.site AND C.Year=tablename.Year AND C.Month=tablename.Month` Then you can use `C.` as needed in your `SELECT` – Jacques Amar Nov 04 '17 at 23:04

MySQL Cost by Category throughout time

1 Answers1