Kurt Peek Kurt Peek - 4 months ago 7
SQL Question

In SQL, select the unique elements of a column whose count exceeds a given number

Suppose I have a table "Orders" like below:

enter image description here

I've entered this into SQLfiddle (http://sqlfiddle.com/#!9/b9d7a/6) as follows:

CREATE TABLE Orders
(`Number` int, `order_date` varchar(10), `cust_id` int, `salesperson_id` int, `Amount` int)
;

INSERT INTO Orders
(`Number`, `order_date`, `cust_id`, `salesperson_id`, `Amount`)
VALUES
(10, '8/2/96', 4, 2, 540),
(20, '1/30/99', 4, 8, 1800),
(30, '7/14/95', 9, 1, 460),
(40, '1/29/98', 7, 2, 2400),
(50, '2/3/98', 6, 7, 600),
(60, '3/2/98', 6, 7, 720),
(70, '5/6/98', 9, 7, 150)
;


I would like to select the elements of
salesperson_id
which occur more than once. Till now I've done

SELECT
salesperson_id
FROM
Orders
GROUP by salesperson_id


but this leads to a selection of all unique elements of
salesperson_id
- that is,
1
,
2
,
7
, and
8
- and not just the ones that occur more than once, i.e.,
2
and
7
. Is there any way I can do
GROUP by
specifying a minimum count number?

Answer
SELECT salesperson_id, count(salesperson_id) as c
FROM Orders 
GROUP by salesperson_id 
HAVING c>1

or even shorter

SELECT salesperson_id FROM Orders GROUP by salesperson_id HAVING count(salesperson_id)>1
Comments