Süleyman K Süleyman K - 3 months ago 16
SQL Question

How to Unite Same PostgreSQL Data

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.

Answer

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.