mahen3d mahen3d - 7 months ago 33
SQL Question

mysql GROUP BY with multiple columns

I have table where date is stored as 3 columns in the table, i.e as below

`periodYear`
`periodMonth`
`billDay`


Which is causing me problems, when i want to generate reports based on the DATE which is a combination of above three. for example

SELECT SUM(amount) as Total,
FROM invoice
WHERE `periodYear` = 2014,
GROUP BY `billDay`,`periodMonth`,`periodYear`


Can somebody help me to explain how to solve this problem ?

For example I want to list all the totals last year on daily base,

If it's a date column, I could have just group by date, but in this case I don't know how to do that, because if you group by billday,..,., then it going group based on the day not DATE.. you see what I mean ?

Answer

You could just concatenate the values together and then group on that:

SELECT SUM(amount) as Total FROM invoice
WHERE periodYear=2014
GROUP BY CONCAT(billDay, '-', periodMonth, '-', periodYear)

Or if you would want to convert to and actual date format for easier sorting afterwards:

SELECT SUM(amount) as Total FROM invoice
WHERE periodYear=2014
GROUP BY CONCAT(periodYear,
                '-',
                LPAD(periodMonth, 2, '00'),
                '-',
                LPAD(billDay, 2, '00')
)