Selecting number of groups (count) in SQL query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bram
Forum Newbie
Posts: 8
Joined: Sat Feb 19, 2011 11:45 am

Selecting number of groups (count) in SQL query?

Post 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).
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

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

Post 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
  )
Bram
Forum Newbie
Posts: 8
Joined: Sat Feb 19, 2011 11:45 am

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

Post by Bram »

Hey, thanks a lot! Works wonders! :)
Post Reply