Page 1 of 1

Selecting number of groups (count) in SQL query?

Posted: Fri Sep 02, 2011 5:21 am
by Bram
Suppose I have an 'Orders' table like this:

Code: Select all

id | customer_id | product_id
---+-------------+------------
1  | 5           | 33
2  | 8           | 57
3  | 2           | 41
4  | 5           | 38
5  | 3           | 19
6  | 8           | 84
7  | 6           | 52
8  | 8           | 60
Here's how I select all customer_ids of customers that have more than one order:

Code: Select all

SELECT customer_id FROM Orders GROUP BY customer_id HAVING COUNT(*)>1

Question: how do I select only the number of customers that have more than one order?

Changing the query from SELECT customer_id into SELECT COUNT(customer_id) or SELECT COUNT(*) results in the number of orders per customer_id, but I just want it to return one value (in the above example that would be 2, because only customer_ids 5 and 8 have more than one order).

Re: Selecting number of groups (count) in SQL query?

Posted: Sat Sep 03, 2011 12:41 pm
by ok
You can use 2 SQL queries:

Code: Select all

SELECT
  COUNT(id)
FROM Orders
WHERE
  customer_id IN (
    SELECT
      customer_id
    FROM Orders
    GROUP BY customer_id
    HAVING COUNT(*)>1
  )

Re: Selecting number of groups (count) in SQL query?

Posted: Sat Sep 10, 2011 4:55 am
by Bram
Hey, thanks a lot! Works wonders! :)