PostgreSQL Query for grouping column values into columns

Question

I have the PostreSQL table shown below. ordered is a boolean column and created_at is a timestamp. I'm trying to fetch rows which tell me the total number of successful orders (count(t)) vs failed ordered (count(f)) as well as the total number of orders (t + f) grouped by day (extracted from created_at)

ordered | created_at t | 2018-10-10 20:13:10 t | 2018-10-10 21:23:11 t | 2018-10-11 06:33:52 f | 2018-10-11 13:13:33 f | 2018-10-11 19:17:11 f | 2018-10-12 00:53:01 f | 2018-10-12 05:14:41 f | 2018-10-12 16:33:09 f | 2018-10-13 17:14:14

I want the following result

created_at | ordered_true | ordered_false | total_orders 2018-10-10 | 2 | 0 | 2 2018-10-11 | 1 | 2 | 3 2018-10-12 | 0 | 3 | 3 2018-10-13 | 0 | 1 | 1

klin · Accepted Answer · 2018-10-29 22:59:44Z

4

Use the aggregate functions sum() and count():

select 
    created_at::date, 
    sum(ordered::int) as ordered_true,
    sum((not ordered)::int) as ordered_false,
    count(*) as total_orders
from my_table
group by 1
order by 1

 created_at | ordered_true | ordered_false | total_orders 
------------+--------------+---------------+--------------
 2018-10-10 |            2 |             0 |            2
 2018-10-11 |            1 |             2 |            3
 2018-10-12 |            0 |             3 |            3
 2018-10-13 |            0 |             1 |            1
(4 rows)

answered Oct 29, 2018 at 22:59

klin

123k15 gold badges240 silver badges262 bronze badges

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

5 Comments

Zain Zafar Over a year ago

Exactly what I wanted. Thanks!

S-Man Over a year ago

Great, love the idea with the int cast!

Marcelo Idemax Over a year ago

@klin do you know how to do it but from a enum type instead of ordered?

klin Over a year ago

@Idemax - You probably need sum(case your_column when 'ordered' then 1 end) instead of sum(ordered::int). If this doesn't help ask a new question (you can put a link here to let me know).

Marcelo Idemax Over a year ago

@klin i added a question if you have time pls :) stackoverflow.com/questions/71942065/…

ArdentLearner · Accepted Answer · 2018-10-30 00:02:16Z

1

Try:

SELECT created_at, 
       COUNT(ordered) filter (where ordered = 't') AS ordered_true,
       COUNT(ordered) filter (where ordered = 'f') AS ordered_false, 
       COUNT(*) AS total_orders 
FROM my_table
GROUP BY created_at

EDIT: use @klint's answer as pointed in the comments by OP grouping by created_at will result in unwanted results as one day will have a couple of groups(timestamp longer than just a day)

edited Oct 30, 2018 at 0:02

answered Oct 29, 2018 at 23:30

ArdentLearner

7428 silver badges20 bronze badges

1 Comment

Zain Zafar Over a year ago

This partly works. GROUP BY created_at includes timestamps so I get multiple rows per day. Using created_at::date as mentioned by klin solves this issue.

Collectives™ on Stack Overflow

PostgreSQL Query for grouping column values into columns

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related