How do I count the customers that made more than a purchase?

I have a table called order that looks like this:

I am trying to display:

1.Total count of customers that bought 1 time only (that went to the store in one occasion, it could have been that they bought multiple items but it is only a one time event)

2.Total count customers that bought more than 1 time (that went to the store in more than an occasion)

I have been executing the following query but the results do not exactly match what I can see from a pivot table. In every shopping sessions there can be multiple rows and multiple items, but there would still be a unique identifier per shopping session and a unique date.

SELECT COUNT(distinct customer_id)

FROM orders AS c

WHERE ( SELECT COUNT(distinct transaction_id)

FROM orders as b

WHERE

    b.customer_id = c.customer_id) =1 

and I substitute the last row with >1 for the customers that shopped more than once

Any help in advance is appreciated

Answer

You can use two levels of aggregation : first count the count of visits per customer (ie on how many distinct days they had at least one transaction), then check how many customers had 1 visit and how many had more.

select
    sum(case when cnt_visits = 1 then 1 else 0 end) customers_with_1_visit,
    sum(case when cnt_visits = 1 then 0 else 1 end) customers_with_more_visits
from (
    select customer_id, count(distinct cast(transaction_date as date)) cnt_visits
    from mytable
    group by customer_id
) t

This uses cast(... as date) to trim the time part of the transaction date. Depending on your database, other options may be available.