mysql GROUP BY with multiple columns

Question

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 1

Try this,

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

Answer 2

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')
)

Answer 3

I think what you want is just the opposite of what you've tried.

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

This will group first by year, then by month, then by day. Biggest to smallest.