How to Unite Same PostgreSQL Data

Question

I have a postgresql statement which is:

( select cast(start_time as date) as time , SUM(count) as  count 
from tbl_product  
where ( cast(start_time as date)  >= '2016-08-30 23:00:00'  and cast(start_time as date)  <= '2016-09-01 20:00:00' ) 
and ( extract(hour from start_time) >= 23  and extract(hour from start_time) <= 24)      
group by time order by time limit 5 )  

UNION ( select cast(start_time as date) as time , SUM(count) as  count 
from tbl_product  
where ( cast(start_time as date)  >= '2016-08-31 23:00:00'  and cast(start_time as date)  <= '2016-09-01 20:00:00' ) 
and ( extract(hour from start_time) >= 0  and extract(hour from start_time) < 20)      
group by time order by time limit 5 )

But it returns the same data for the same date, because of a UNION statement

time            count
date            numeric
"2016-08-31"    543595
"2016-08-31"    3666277
"2016-09-01"    3365093

How can I add these data values like:

time            count
date            numeric
"2016-08-31"    4209872
"2016-09-01"    3365093

Thanks for helping.

redneb · Accepted Answer · 2016-09-01 09:03:48Z

You need to move the GROUP BY out of the individual queries. Something like that:

SELECT time, SUM(count) as  count FROM (
    ( select cast(start_time as date) as time , count 
    from tbl_product  
    where ( cast(start_time as date)  >= '2016-08-30 23:00:00'  and cast(start_time as date)  <= '2016-09-01 20:00:00' ) 
    and (extract(hour from start_time) >= 23))  
        UNION ALL
    ( select cast(start_time as date) as time , count 
    from tbl_product  
    where ( cast(start_time as date)  >= '2016-08-31 23:00:00'  and cast(start_time as date)  <= '2016-09-01 20:00:00' ) 
    and ( extract(hour from start_time) >= 0  and extract(hour from start_time) < 20))
    ) AS t
GROUP BY time ORDER by time;

I've also changed the UNION to a UNION ALL, because it seems to make more sense in this case. Finally, the test extract(hour from start_time) <= 24 is always true, so it's redundant.

Piotr Rogowski · Accepted Answer · 2016-09-01 09:04:02Z

try this query:

select
exe.time_,
sum(exe.count_)
from
(
    select cast(start_time as date) as time_ , SUM(count) as  count_ 
    from tbl_product  
    where ( cast(start_time as date)  >= '2016-08-30 23:00:00'  and cast(start_time as date)  <= '2016-09-01 20:00:00' ) 
    and ( extract(hour from start_time) >= 23  and extract(hour from start_time) <= 24)      
    group by time order by time limit 5   
    UNION 
    select cast(start_time as date) as time_, SUM(count) as  count_
    from tbl_product  
    where ( cast(start_time as date)  >= '2016-08-31 23:00:00'  and cast(start_time as date)  <= '2016-09-01 20:00:00' ) 
    and ( extract(hour from start_time) >= 0  and extract(hour from start_time) < 20)      
    group by time order by time limit 5 
) exe
group by exe.time_

Collectives™ on Stack Overflow

How to Unite Same PostgreSQL Data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related