1

I have a query which I get the customers that have more than 1 orders However, I would like to get the number of customer that has only 1 orders as well and divide them like 1 orders / more than 1 order

So that my table will have 3 colums

one_order, more_than_one_order, divided

I tried different approaches like duplicating the query like =1 but it is not the proper way

Query

SELECT COUNT(*)
FROM (
select customer_id from orders GROUP BY customer_id HAVING COUNT(order_id)>1
) A
3

You can use a case expression like:

SELECT count(case when cnt = 1 then 1 end) as one_order
     , count(case when cnt > 1 then 1 end) as more_than_one_order
FROM (
    select customer_id, count(1) as cnt from orders GROUP BY customer_id
) as T

You can also use a filter clause for the count aggregates:

SELECT count(1) filter (where cnt = 1) as one_order
     , count(1) filter (where cnt > 1) as more_than_one_order
FROM ...

For the third attribute you can either reuse the aggregates:

select count(1) filter (where cnt = 1) as ...
     , count(1) filter (where cnt > 1) as ...
     , count(1) filter (where cnt = 1) / count(1) filter (where cnt > 1) as ...

or add another level of nesting:

select one_order
     , more_than_one_order
     , one_order / more_than_one_order 
from (
    SELECT count(1) filter (where cnt = 1) as one_order
         , count(1) filter (where cnt > 1) as more_than_one_order
    FROM (
        select customer_id, count(1) as cnt 
        from orders 
        GROUP BY customer_id
    ) as T1
) as T2
  • Works better than expected! With only 1 missing. I just need to printer one_order/more_than_one_order – SNaRe Mar 30 '18 at 9:20
  • Which of these gr8 options would work faster? – SNaRe Mar 30 '18 at 11:49
  • 1
    @SNaRe: Regardless of what we might say, in the end it's always better to test in your own environment, so just pick any option (start with one that you consider simpler to understand/maintain) and see if it's fast enough for you. If performance is a problem and you need help with that, feel free to post a new question. – Andriy M Apr 2 '18 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.