CODEWITHSUNDEEP
mysql
Kurt Peek
Kurt Peek

Reputation: 57471

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?

Upvotes: 1

Views: 46

Answers (1)

olegsv
olegsv

Reputation: 1462

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

Upvotes: 3

Related Questions