CODEWITHSUNDEEP
sqlpostgresqldatatablecountpivot
MtnBee
MtnBee

Reputation: 37

Combine Output of Queries in Pivot Table with PostgreSQL

Suppose I have a table called orders that looks like this:

id order date Orders_Wanted Orders_Given
1 2020-11-29 19:12:44.417 2 6
1 2020-11-29 20:12:44.417 2 6
1 2020-11-30 23:37:28.692 8 2
1 2020-11-30 23:37:28.692 2 6

How do I write a query that shows the count of orders_wanted - orders_given by hour broken down into two columns, one that counts positive results and one that counts negative results (a note that orders_wanted and orders_given are times, so that's why I am calculating orders_wanted - orders_given). I would also like to add a final column that calculates the percentage of total orders per hour that are positive (count_orders_positive/ (count_orders_negative + Count_orders_positive)).

The output of the query would look something like this:

week day hour count_orders_positive count_orders_negative Percentage_orders_positive
48 7 19 0 1 100%
48 7 20 0 1 100%
49 1 23 1 1 50%

So far I am able to get the bottom two results using these queries, but I don't know how to combine them.

SELECT  
extract (week from (order_date at time zone 'MST' at time zone 'UTC') ) as "week", 
extract (isodow from (order_date at time zone 'MST' at time zone 'UTC') ) as "day",
extract (hour from (order_date at time zone 'MST' at time zone 'UTC') ) as "hour",
Count (extract (hour from (order_date at time zone 'MST' at time zone 'UTC') )) as 
"count_orders_positive" 
from orders  
WHERE orders_wanted - orders_given >= 0
group by week, day, hour
order by week, day, hour;
week day hour count_orders_positive
49 1 23 1 
SELECT
extract (week from (order_date at time zone 'MST' at time zone 'UTC') ) as "week", 
extract (isodow from (order_date at time zone 'MST' at time zone 'UTC') ) as "day",
extract (hour from (order_date at time zone 'MST' at time zone 'UTC') ) as "hour",
Count (extract (hour from (order_date at time zone 'MST' at time zone 'UTC') )) as 
"count_orders_negative" 
from orders  
WHERE orders_wanted - orders_given < 0
group by week, day, hour
order by week, day, hour;
week day hour count_orders_negative
48 7 19 1
48 7 20 1
49 1 23 1

Upvotes: 0

Views: 27

Answers (1)

GMB
GMB

Reputation: 222492

You can do conditional aggregation. avg() comes handy to compute the percentage:

select  
    extract (week   from (order_date at time zone 'MST' at time zone 'UTC') ) as "week", 
    extract (isodow from (order_date at time zone 'MST' at time zone 'UTC') ) as "day",
    extract (hour   from (order_date at time zone 'MST' at time zone 'UTC') ) as "hour",
    count(*) filter (where orders_wanted - orders_given >= 0) as count_orders_positive,
    count(*) filter (where orders_wanted - orders_given <  0) as count_orders_negative,
    100 * avg((orders_wanted - orders_given >= 0)::int) as percent_orders_positive
from orders
group by week, day, hour
order by week, day, hour;

Upvotes: 1

Related Questions