CODEWITHSUNDEEP
mysqlsqlcase
user1123417
user1123417

Reputation: 173

Time slots using Case in MySql

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!!

Upvotes: 1

Views: 454

Answers (1)

eggyal
eggyal

Reputation: 125865

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

Upvotes: 1

Related Questions