CODEWITHSUNDEEP
sqlsql-server
Timm
Timm

Reputation: 11

SQL Query that returns only records with multiple rows

I have a database with two tables: Customers and Services.

The Customer table has account information and the Services table has individual records of every service my company has performed.

The Services table is linked to the Customer table with an ID column where Customers.ID = Services.CustomerID.

I am trying to perform a query which will return the customer name and account number for only those customers where more than one service has been performed within a specified date range. For example, show me all customers where I've performed work more than twice in 2015.

How do I write this type of query so that only those with multiple service visits are returned? I can do this using cursors, counting results, etc. but it seems there must be a more efficient way.

Upvotes: 0

Views: 599

Answers (3)

pepin
pepin

Reputation: 1

SELECT Customers.CustomerID
,Customers.Name
,Customers.AcoountNumber
FROM Customers 
JOIN 
(
    select Services.CustomerID, Services.CustomerID as totalServices
    from Services
    where Services.date BETWEEN @FROM_DATE AND @TO_DATE
    group by Services.CustomerID
    having count(Services.CustomerID) >1
) AS CustomersMoreThan1 on Customers.CustomerID=CustomersMoreThan1.CustomerID

This query allows you to add more columns from your Customer table without having to modify your GROUP BY.

Upvotes: 0

Lucero
Lucero

Reputation: 60190

Just use a JOIN and a GROUP BY like so:

SELECT Customers.ID
FROM Customers
JOIN Services ON Customer.ID=Services.CustomerID
--WHERE your Services date check here
GROUP BY Customers.ID -- you can add more columns from the customers table here and in the select as needed
HAVING SUM(1)>1

You can add a WHERE clause to filter the Services' date range (just before the GROUP BY).

Upvotes: 2

hatane
hatane

Reputation: 34

Select Id from Customers C where count (select * from Services S where C.id = S.CustomersId ) > 1

Upvotes: 0

Related Questions