CODEWITHSUNDEEP
sqlsql-server
glitterati
glitterati

Reputation: 133

Custom Order By in SQL

I've written a query for getting me the total sum of a column id for every hour. The hour value is calculated from a date field in the table called, created_at. I have used an if case for better understanding of the hour value result. However, when I order by the hour (now a string after using case), I don't get the best results. I want to order by the actual hour and not by the string version of it. How can I best do it?

I've tried using 

order by case when DATEPART(hour,created_at) = 0 then 0
when DATEPART(hour,x.created_at) = 1 then 1
when DATEPART(hour,x.created_at) = 2 then 2
when DATEPART(hour,x.created_at) = 3 then 3
when DATEPART(hour,x.created_at) = 4 then 4
when DATEPART(hour,x.created_at) = 5 then 5
when DATEPART(hour,x.created_at) = 6 then 6
when DATEPART(hour,x.created_at) = 7 then 7
when DATEPART(hour,x.created_at) = 8 then 8
when DATEPART(hour,x.created_at) = 9 then 9
when DATEPART(hour,x.created_at) = 10 then 10
when DATEPART(hour,x.created_at) = 11 then 11
when DATEPART(hour,x.created_at) = 12 then 12
when DATEPART(hour,x.created_at) = 13 then 13
when DATEPART(hour,x.created_at) = 14 then 14
when DATEPART(hour,x.created_at) = 15 then 15
when DATEPART(hour,x.created_at) = 16 then 16
when DATEPART(hour,x.created_at) = 17 then 17
when DATEPART(hour,x.created_at) = 18 then 18
when DATEPART(hour,x.created_at) = 19 then 19
when DATEPART(hour,x.created_at) = 20 then 20
when DATEPART(hour,x.created_at) = 21 then 21
when DATEPART(hour,x.created_at) = 22 then 22
when DATEPART(hour,x.created_at) = 23 then 23
end

Only to get an error that created_at must be in a group by query.

The following is my original query that needs to be edited.

select case 
    when DATEPART(hour,created_at) = 0 then '12 AM to 1 AM'
    when DATEPART(hour,created_at) = 1 then '1 AM to 2 AM'
    when DATEPART(hour,created_at) = 2 then '2 AM to 3 AM'
    when DATEPART(hour,created_at) = 3 then '3 AM to 4 AM'
    when DATEPART(hour,created_at) = 4 then '4 AM to 5 AM'
    when DATEPART(hour,created_at) = 5 then '5 AM to 6 AM'
    when DATEPART(hour,created_at) = 6 then '6 AM to 7 AM'
    when DATEPART(hour,created_at) = 7 then '7 AM to 8 AM'
    when DATEPART(hour,created_at) = 8 then '8 AM to 9 AM'
    when DATEPART(hour,created_at) = 9 then '9 AM to 10 AM'
    when DATEPART(hour,created_at) = 10 then '10 AM to 11 AM'
    when DATEPART(hour,created_at) = 11 then '11 AM to 12 PM'
    when DATEPART(hour,created_at) = 12 then '12 PM to 1 PM'
    when DATEPART(hour,created_at) = 13 then '1 PM to 2 PM'
    when DATEPART(hour,created_at) = 14 then '2 PM to 3 PM'
    when DATEPART(hour,created_at) = 15 then '3 PM to 4 PM'
    when DATEPART(hour,created_at) = 16 then '4 PM to 5 PM'
    when DATEPART(hour,created_at) = 17 then '5 PM to 6 PM'
    when DATEPART(hour,created_at) = 18 then '6 PM to 7 PM'
    when DATEPART(hour,created_at) = 19 then '7 PM to 8 PM'
    when DATEPART(hour,created_at) = 20 then '8 PM to 9 PM'
    when DATEPART(hour,created_at) = 21 then '9 PM to 10 PM'
    when DATEPART(hour,created_at) = 22 then '10 PM to 11 PM'
    when DATEPART(hour,created_at) = 23 then '11 PM to 12 AM'
    end as hour_of_day, count(id) as count_total 
from myTable 
where  CAST(created_at AS DATE) = CAST((getdate()-1) AS DATE) 
group by 1

The results should be ordered as follows - 

hour_of_day      count_total
12 AM to 1 AM    1
1 AM to 2 AM     20
2 AM to 3 AM     23
...
6PM to 7 PM      9
...
11PM to 12AM     78

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In all likelihood, the simplest solution is just:

order by min(x.created_at)

This might not work if the minimum created_at are spread over multiple days. This will definitely work:

order by min(datepart(hour, x.created_at))

Upvotes: 2

forpas
forpas

Reputation: 164214

I believe your dbms is SQL Server, so use a CTE that performs the grouping and then select from the cte:

with cte as (
  select  
    DATEPART(hour, created_at) as hourofday, 
    count(id) as count_total from myTable 
  where CAST(created_at AS DATE) = CAST((getdate()-1) AS DATE) 
  group by DATEPART(hour,created_at)
)
select case 
when hourofday = 0 then '12 AM to 1 AM'
when hourofday = 1 then '1 AM to 2 AM'
when hourofday = 2 then '2 AM to 3 AM'
when hourofday = 3 then '3 AM to 4 AM'
when hourofday = 4 then '4 AM to 5 AM'
when hourofday = 5 then '5 AM to 6 AM'
when hourofday = 6 then '6 AM to 7 AM'
when hourofday = 7 then '7 AM to 8 AM'
when hourofday = 8 then '8 AM to 9 AM'
when hourofday = 9 then '9 AM to 10 AM'
when hourofday = 10 then '10 AM to 11 AM'
when hourofday = 11 then '11 AM to 12 PM'
when hourofday = 12 then '12 PM to 1 PM'
when hourofday = 13 then '1 PM to 2 PM'
when hourofday = 14 then '2 PM to 3 PM'
when hourofday = 15 then '3 PM to 4 PM'
when hourofday = 16 then '4 PM to 5 PM'
when hourofday = 17 then '5 PM to 6 PM'
when hourofday = 18 then '6 PM to 7 PM'
when hourofday = 19 then '7 PM to 8 PM'
when hourofday = 20 then '8 PM to 9 PM'
when hourofday = 21 then '9 PM to 10 PM'
when hourofday = 22 then '10 PM to 11 PM'
when hourofday = 23 then '11 PM to 12 AM'
end as hour_of_day, 
count_total 
from cte
order by hourofday

Upvotes: 3

Related Questions