Sql query to display records that appear more than once in a table

Question

I have two tables, Customer with columns CustomerID, FirstName, Address and Purchases with columns PurchaseID, Qty, CustomersID.

I want to create a query that will display FirstName(s) that have bought more than two products, product quantity is represented by Qty.

I can't seem to figure this out - I've just started with T-SQL

If you are just getting started, you should edit the question and show some attempt. — Gordon Linoff, Oct 16 '16 at 20:28

score 0 · Answer 1 · answered Oct 16 '16 at 20:37

You could sum the purchases and use a having clause to filter those you're interested in. You can then use the in operator to query only the customer names that fit these IDs:

SELECT FirstName
FROM   Customer
WHERE  CustomerID IN (SELECT   CustomerID
                      FROM     Purchases
                      GROUP BY CustomerID
                      HAVING   SUM(Qty) > 2)

user1029979 · Answer 2 · 2016-10-16T21:50:10.273

0

Please try this:

select c.FirstName,p.Qty
from Customer as c
join Purchase as p
on c.CustomerID = p.CustomerID
where CustomerID in (select CustomerID from Purchases group by CustomerID having count(CustomerID)>2);

edited Oct 16 '16 at 21:50

answered Oct 16 '16 at 20:44

user1029979

9
3

score 0 · Answer 3 · answered Oct 16 '16 at 20:45

0

Please try this, it should work for you, according to your question.

Select MIN(C.FirstName) FirstName  from Customer C INNER JOIN Purchases P ON C.CustomerID=P.CustomersID Group by P.CustomersID Having SUM(P.Qty) >2

answered Oct 16 '16 at 20:45

Bhanu Pratap

1,635
17
17

I generally like this mechanism for repetitive data but because FirstName will just be repeated and you are already doing a group by just group by it. – Matt Oct 16 '16 at 22:57

score 0 · Answer 4 · edited May 23 '17 at 12:06

SELECT
    c.FirstName
FROM
    Customer c
    INNER JOIN Purchases p
    ON c.CustomerId = p.CustomerId
GROUP BY
    c.FirstName
HAVING
    SUM(p.Qty) > 2

While the IN suggestions would work they are kind of overkill and more than likely less performant than a straight up join with aggregation. The trick is the HAVING Clause by using it you can limit your result to the names you want. Here is a link to learn more about IN vs. Exists vs JOIN (NOT IN vs NOT EXISTS)

There are dozens of ways of doing this and to introduce you to Window Functions and common table expressions which are way over kill for this simplified example but are invaluable in your toolset as your queries continue to get more complex:

;WITH cte AS (
    SELECT DISTINCT
       c.FirstName
       ,SUM(p.Qty) OVER (PARTITION BY c.CustomerId) as SumOfQty
    FROM
       Customer c
       INNER JOIN Purchases p
       ON c.CustomerId = p.CustomerId
)

SELECT *
FROM
    cte
WHERE
     SumOfQty > 2

Sql query to display records that appear more than once in a table

4 Answers4