CODEWITHSUNDEEP
mysql
mahen3d
mahen3d

Reputation: 7724

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 ?

Upvotes: 1

Views: 128

Answers (3)

Robby Cornelissen
Robby Cornelissen

Reputation: 97120

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

Upvotes: 2

cyk
cyk

Reputation: 581

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.

Upvotes: 0

Vinu Sebastian
Vinu Sebastian

Reputation: 568

Try this,

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

Upvotes: 3

Related Questions