CODEWITHSUNDEEP
mysqlgroup-byrows
armze3
armze3

Reputation: 55

Group months as season

I am stuck on a mysql query. I was wondering how I can group by multiple rows. I got the months and I wanted to group them together by season, so for example I want to show the crime count by adding all the crimes in the month from 01-03 and show this as winter etc.

this is the code I currently have 

SELECT d.month, SUM(c.crimeCount)
FROM FYP_Date d JOIN FYP_Crime c
WHERE d.dateID=c.dateID
GROUP BY month

enter image description here

Upvotes: 1

Views: 337

Answers (3)

recycler
recycler

Reputation: 1471

SELECT d.month, SUM(c.crimeCount), CONVERT(LEFT(d.month, 4), SIGNED) AS y, CONVERT(RIGHT(d.month, 2), SIGNED) AS m, CEIL(CONVERT(RIGHT(d.month, 2), SIGNED)/3) AS q 
FROM FYP_Date d JOIN FYP_Crime c WHERE d.dateID=c.dateID GROUP BY q;

For year and quarter you have to compine "y" and "q"

SELECT d.month, SUM(c.crimeCount), CONVERT(LEFT(d.month, 4), SIGNED) AS y, CONVERT(RIGHT(d.month, 2), SIGNED) AS m, CEIL(CONVERT(RIGHT(d.month, 2), SIGNED)/3) AS q 
FROM FYP_Date d JOIN FYP_Crime c WHERE d.dateID=c.dateID GROUP BY y, q;

The original question was how to convert month to quarter, which was edited (see pictures). The solution for this is:

SELECT d.month, SUM(c.crimeCount), CEIL(month/3) AS q 
FROM FYP_Date d JOIN FYP_Crime c WHERE d.dateID=c.dateID GROUP BY q

After defining column "month" to "year-month", the solution is mentioned above (updated).

Upvotes: 0

PerlDuck
PerlDuck

Reputation: 5730

Probably the QUARTER function is of help here:

select quarter(concat(d.month,'-01')) as qrt, 
       sum(c.crimecount)
  from fyp_date d join fyp_crime c on d.dateid=c.dateid
 group by qrt;

This is not the same as season but it's quite close. I assume your month column is char so I appended a '-01' for the calculation because quarter needs (at least) a day to work with.

Upvotes: 3

sgeddes
sgeddes

Reputation: 62831

Here's one option using a case statement with month:

select case when month(d.month) in (1,2,3) then 'q1',
            when month(d.month) in (4,5,6) then 'q2',
            when month(d.month) in (7,8,9) then 'q3',
            when month(d.month) in (10,11,12) then 'q4'
       end as quarter,
       SUM(c.crimeCount)
FROM FYP_Date d 
    JOIN FYP_Crime c ON d.dateID=c.dateID
GROUP BY 1

Also please note -- when joining, use on to match your records instead of using where.

Upvotes: 1

Related Questions