1

I'm trying to sum on fields by 3 time slots with this code:

SELECT 
CASE when TIME(v.time) between cast('00:00:00' as time) and cast('06:00:00' as time) then '00:00:00 - 06:00:00'
when TIME(v.time) between cast('06:00:00' as time) and cast('16:00:00' as time) then '06:00:00 - 16:00:00'
when TIME(v.time) between cast('16:00:00' as time) and cast('00:00:00' as time) then '16:00:00 - 00:00:00' End as 'TimeSlot',
sum(v.resting)
FROM vital_activity_histogram v
GROUP BY 'TimeSlot'

The result I want is(for example):

time slot              | Sum(v.resting)
00:00:00 - 06:00:00        24
06:00:00 - 16:00:00        56
16:00:00 - 00:00:00        72

what I get when I run the query is:

time slot              | Sum(v.resting)
00:00:00 - 06:00:00        24

And I have data inside the other time slots. Anyone know how to fix this? Thanks!!

user1123417
  • 163
  • 1
  • 2
  • 10
  • Just a tip, you can do "case TIME(v.time) when between cast... when between... end", i.e. you don't have to specify TIME(v.time) more than once. (At least in ANSI SQL....) – jarlh Mar 09 '15 at 09:34

1 Answers1

1

You need to GROUP BY TimeSlot, not GROUP BY 'TimeSlot': as things stand you're grouping on a string literal, which is constant (and therefore every record is in the same group).

See also When to use single quotes, double quotes, and backticks in MySQL

Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237