CODEWITHSUNDEEP
mysqlsqldategroup-by
Aquaholic
Aquaholic

Reputation: 883

Quarter Equivalent for MySQL Data Summation

I am using the following mySQL query for summing up "volume" column for each month

SELECT DATE_FORMAT(date, "%m-%Y") AS Month, 
       SUM(vol) 
FROM tbl 
GROUP BY DATE_FORMAT(date, "%m-%Y") 
order by Month

It produces the right output as expected on monthly basis:

enter image description here

(Above is image capture from excel)

Now, I want to change it to quarterly (i.e. 3-monthly) summation, or six-monthly summation.

What changes are needed to the above query? I am unable to find any equivalent of %m for quarter or for six-month, hence the question.

Upvotes: 0

Views: 234

Answers (1)

GMB
GMB

Reputation: 222502

If you want this per quarter, you can do:

select year(date) yr, quarter(date) qr, sum(vol) sum_vol
from tbl
group by yr, qr
order by yr, qr

Or if you want to concatenate the year and quarter in a single column:

select concat(year(date), '-Q', quarter(date)) yr_qr, sum(vol) sum_vol
from tbl
group by yr_qr
order by yr_qr

To split by semester, you need a little more work:

select 
    concat(year(date), '-S', case when month(date) between 1 and 6 then 1 else 2 end) yr_sr, 
    sum(vol) 
from tbl
group by yr_sr
order by yr_sr

Upvotes: 2

Related Questions