Sum is not working in postgres. (Switching from mysql syntax to postgres)-CodePudding

I have this in Mysql and it works perfectly fine. Switching my project from mysql to postgres:

SELECT 
    
CASE
    WHEN EXTRACT(HOUR FROM t_stamp) >= 5 AND EXTRACT(HOUR FROM t_stamp) < 6 THEN '5-6'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 6 AND EXTRACT(HOUR FROM t_stamp) < 7 THEN '6-7'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 7 AND EXTRACT(HOUR FROM t_stamp) < 8 THEN '7-8'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 8 AND EXTRACT(HOUR FROM t_stamp) < 9 THEN '8-9'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 9 AND EXTRACT(HOUR FROM t_stamp) < 10 THEN '9-10'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 10 AND EXTRACT(HOUR FROM t_stamp) < 11 THEN '10-11'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 11 AND EXTRACT(HOUR FROM t_stamp) < 12 THEN '11-12'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 12 AND EXTRACT(HOUR FROM t_stamp) < 13 THEN '12-13'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 13 AND EXTRACT(HOUR FROM t_stamp) < 14 THEN '13-14'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 14 AND EXTRACT(HOUR FROM t_stamp) < 15 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 15 AND EXTRACT(HOUR FROM t_stamp) < 16 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 16 AND EXTRACT(HOUR FROM t_stamp) < 17 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 17 AND EXTRACT(HOUR FROM t_stamp) < 18 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 18 AND EXTRACT(HOUR FROM t_stamp) < 19 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 19 AND EXTRACT(HOUR FROM t_stamp) < 20 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 20 AND EXTRACT(HOUR FROM t_stamp) < 21 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 21 AND EXTRACT(HOUR FROM t_stamp) < 22 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 22 AND EXTRACT(HOUR FROM t_stamp) < 23 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 23 AND EXTRACT(HOUR FROM t_stamp) < 24 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 24 AND EXTRACT(HOUR FROM t_stamp) < 0 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 0 AND EXTRACT(HOUR FROM t_stamp) < 1 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 1 AND EXTRACT(HOUR FROM t_stamp) < 2 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 2 AND EXTRACT(HOUR FROM t_stamp) < 3 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 3 AND EXTRACT(HOUR FROM t_stamp) < 4 THEN '14-15'
    WHEN EXTRACT(HOUR FROM t_stamp) >= 4 AND EXTRACT(HOUR FROM t_stamp) < 5 THEN '14-15'
   
END as onehour,


CASE
    WHEN EXTRACT(HOUR FROM t_stamp) >= 5 AND EXTRACT(HOUR FROM t_stamp) < 6 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 6 AND EXTRACT(HOUR FROM t_stamp) < 7 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 7 AND EXTRACT(HOUR FROM t_stamp) < 8 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 8 AND EXTRACT(HOUR FROM t_stamp) < 9 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 9 AND EXTRACT(HOUR FROM t_stamp) < 10 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 10 AND EXTRACT(HOUR FROM t_stamp) < 11 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 11 AND EXTRACT(HOUR FROM t_stamp) < 12 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 12 AND EXTRACT(HOUR FROM t_stamp) < 13 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 13 AND EXTRACT(HOUR FROM t_stamp) < 14 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 14 AND EXTRACT(HOUR FROM t_stamp) < 15 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 15 AND EXTRACT(HOUR FROM t_stamp) < 16 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 16 AND EXTRACT(HOUR FROM t_stamp) < 17 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >=17 AND EXTRACT(HOUR FROM t_stamp) < 18 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 18 AND EXTRACT(HOUR FROM t_stamp) < 19 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 19 AND EXTRACT(HOUR FROM t_stamp) < 20 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 20 AND EXTRACT(HOUR FROM t_stamp) < 21 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 21 AND EXTRACT(HOUR FROM t_stamp) < 22 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 22 AND EXTRACT(HOUR FROM t_stamp) < 23 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 23 AND EXTRACT(HOUR FROM t_stamp) < 24 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 24 AND EXTRACT(HOUR FROM t_stamp) < 0 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 0 AND EXTRACT(HOUR FROM t_stamp) < 1 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 1 AND EXTRACT(HOUR FROM t_stamp) < 2 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 2 AND EXTRACT(HOUR FROM t_stamp) < 3 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 3 AND EXTRACT(HOUR FROM t_stamp) < 4 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)
    WHEN EXTRACT(HOUR FROM t_stamp) >= 4 AND EXTRACT(HOUR FROM t_stamp) < 5 THEN sum(case when "FullSheetNumber" != 0  then 1 else 0 end)  
    
END as total_eachhour


FROM group_table

GROUP BY onehour,t_stamp
ORDER BY onehour

and the current outcome: enter image description here

But what I really want is something like:

onehour	total_eachhour
10-11	56
11-12	32

CodePudding user response：

Your root problem is: GROUP BY onehour,t_stamp it should only be GROUP BY onehour

However, your query can be simplified. You don't need the case expression for the count(), and I would use a proper time value to define the hour range:

The conditional aggregation can be done using filter() in Postgres which I think is more readable:

So the query would look like this:

select case
         when t_stamp::time >= '05:00' and t_stamp::time < '06:00' then '5-6'
         when t_stamp::time >= '06:00' and t_stamp::time < '07:00' then '6-7'
         when t_stamp::time >= '07:00' and t_stamp::time < '08:00' then '7-8'
         when t_stamp::time >= '08:00' and t_stamp::time < '09:00' then '8-9'
         when t_stamp::time >= '09:00' and t_stamp::time < '10:00' then '9-10'
         ....
       end as one_our, 
       count(*) filter (where "FullSheetNumber" <> 0 ) as total_eachhour
from group_table
group by one_our;