PostgreSQL Query to get pivot result

Question

I have a log table look like this

rpt_id | shipping_id | shop_id | status | create_time
-------------------------------------------------------------
1      | 1           | 600     | 1      | 2013-12-01 01:06:50
2      | 1           | 600     | 0      | 2013-12-01 01:06:55
3      | 1           | 600     | 1      | 2013-12-02 10:00:30
4      | 2           | 600     | 1      | 2013-12-02 10:00:30
5      | 1           | 601     | 1      | 2013-12-02 11:20:10
6      | 2           | 601     | 1      | 2013-12-02 11:20:10
7      | 1           | 601     | 0      | 2013-12-03 09:10:10
8      | 3           | 602     | 1      | 2013-12-03 13:15:58

And I want to use single query to make it look like this

shipping_id | total_activate | total_deactivate
-----------------------------------------------
1           | 2              | 2
2           | 2              | 0
3           | 1              | 0

How should I query this?

Note:

Status = 1 = Activate
Status = 0 = Deactivate
Count total activate / deactivate rule: look at log table above. rpt_id 1 & 3, it has same shop_id, shipping_id and status. It should only count as one. See the result table. Shipping id 1 is only activated by 2 shops, they are shop_id 600 and 601.

Can you guys advice me how to make the query? thanks for the help:D

No time to answer right now, but search for "crosstab"

Craig Ringer
– Craig Ringer

2013-12-12 03:24:09 +00:00
Commented Dec 12, 2013 at 3:24 — Craig Ringer
– Craig Ringer, Commented Dec 12, 2013 at 3:24

Jorge Campos · Accepted Answer · 2013-12-12 03:32:56Z

1

Try this:

select shipping_id,
       sum(case when status=1 then 1 else 0 end) as total_activate,
       sum(case when status=0 then 1 else 0 end) as total_deactivate
from (select distinct shipping_id, 
                      shop_id, 
                      status
        from test) a
group by shipping_id
order by shipping_id

See it here at fiddle: http://sqlfiddle.com/#!15/f15fd/4

I did not put the date on the query as it is not important for the result.

answered Dec 12, 2013 at 3:32

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lucky-Ong Ong · Accepted Answer · 2013-12-12 04:23:27Z

1

Yes thanks... I also figured it out already, you can do it this way too.... thx

SELECT 
    shipping_id, 
    COUNT(DISTINCT CASE WHEN status = 1 THEN shop_id END) AS total_activate, 
    COUNT(DISTINCT CASE WHEN status = 0 THEN shop_id END) AS total_deactivate 
FROM 
    test

GROUP BY 
    shipping_id 
ORDER BY 
    shipping_id

answered Dec 12, 2013 at 4:23

Lucky-Ong Ong

357 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Query to get pivot result

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