How pivot a sum of values

Question

i have a question, How can i pivot an aggregation result to look like ..

I'm trying to pivot a simple aggregation using this query first:

select sync_action action, count(sync_action) total
FROM my_table
group by sync_action

and to pivot the table i'm using:

select * from (  
    select sync_action , count(sync_action) total
    FROM my_table
    group by sync_action  )  
    pivot
    (    
    count(sync_action)
    for sync_action in ('delete','create') 
    )
;

and i don't know where is the error, because the result is:

the idea is have the same as the first image.

Can somebody help me?

Best regards

Answer 1

I would just use conditional aggregation:

select 
    sum(case when sync_action = 'delete' then total else 0 end) sum_delete,
    sum(case when sync_action = 'create' then total else 0 end) sum_create
from mytable
where sync_action in ('delete', 'create')

Answer 2

You don't need to do group by, just do like

SELECT *
FROM mytable
pivot 
( COUNT(sync_action) 
  FOR sync_action IN('delete','create')
);

Answer 3

In your query you need "SUM of total" instead of "Count of sync_action" in pivot section. Others are ok. If you use count, in your case you will always get 1.

select * from (  
    select sync_action , count(sync_action) total
    FROM my_table
    group by sync_action  ) as p
    pivot
    (    
    sum(p.total)
    for p.sync_action in ("delete","create") 
    )pt

Answer 4

I think you just want:

select sum(case when sync_action = 'delete' then 1 else 0 end) as delete,
       sum(case when sync_action = 'create' then 1 else 0 end) as create
from my_table;

I don't see how pivot helps at all with what you want to do.