1

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

ekad
  • 14,436
  • 26
  • 44
  • 46
armze3
  • 47
  • 1
  • 1
  • 7

3 Answers3

3

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.

PerlDuck
  • 5,610
  • 3
  • 20
  • 39
1

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.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Won't work (at least with my MySQL) because `select month('2016-01');` gives `NULL`. But +1 for the JOIN…ON. – PerlDuck Mar 01 '16 at 20:03
  • @PerlDog -- ah, I assumed it was a date field -- didn't see the edits. The month would have to be converted accordingly if that's the case. I actually prefer your solution -- never used `quarter` before. +1 to you for that! – sgeddes Mar 01 '16 at 20:05
  • Actually I haven't either. :-) Never needed it. Yours has the benefit that the OP can change the values to e.g. `... in (11,12,1) then 'winter'`. – PerlDuck Mar 01 '16 at 20:07
0
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).

recycler
  • 1,301
  • 9
  • 9