Search code examples
sqlverticasubtotal

SQL How to Query Total & Subtotal

I have a table looks like below where day, order_id, and order_type are stored.

select day, order_id, order_type
from sample_table
day order_id order_type
2021-03-01 1 offline
2021-03-01 2 offline
2021-03-01 3 online
2021-03-01 4 online
2021-03-01 5 offline
2021-03-01 6 offline
2021-03-02 7 online
2021-03-02 8 online
2021-03-02 9 offline
2021-03-02 10 offline
2021-03-03 11 offline
2021-03-03 12 offline

Below is desired output:

day total_order num_offline_order num_online_order
2021-03-01 6 4 2
2021-03-02 4 2 2
2021-03-03 2 2 0

Does anybody know how to query to get the desired output?

Solution

  • Use case and sum:

    select day, 
    count(1) as total_order
    sum(case when order_type='offline' then 1 end) as num_offline_order,
    sum(case when order_type='online' then 1 end) as num_online_order
from sample_table
group by day
order by day