Table Select one column and divide into two different-CodePudding

I have a table like this let's call it action:

id | amt | time_stamp | act_id |
--- ----- ------------ -------- 
int| int | timestamp  | int    |

and another table (let's call it action_type):

id | name
--- -----
int| str

in which (and this is important) are two entries: give and take

Now what i need to do is create select statement where at the end i'll have result like this:

sum_give | sum_take | time_stamp |
--------- ---------- ------------

where sum give is

select 
sum(amt) as sum_give,
cast (date_trunc('hour', time_stamp) as timestamp) as hr
from action
where act_id=1
group_by date_trunc('hour' ...);

and the other is

select 
sum(amt) as sum_take,
cast (date_trunc('hour', time_stamp) as timestamp) as hr
from action
where act_id=2
...

you get the picture...
I don't know how to create the result i need

CodePudding user response：

Use conditional aggregation here:

SELECT
    SUM(amt) FILTER (WHERE act_id = 1) AS sum_give,
    SUM(amt) FILTER (WHERE act_id = 2) AS sum_take,
    CAST(DATE_TRUNC('hour', time_stamp) AS timestamp) AS hr
FROM action
GROUP BY 3;

CodePudding user response：

It seems you simply need a CASE statement -

SELECT sum(CASE WHEN act_id=1 THEN amt END) as sum_give,
       sum(CASE WHEN act_id=2 THEN amt END) as sum_take,
       cast (date_trunc('hour', time_stamp) as timestamp) as hr
  FROM action
 WHERE act_id IN (1, 2)
 GROUP BY date_trunc('hour' ...);