Thread: Detecting joined records that number only 1

1. Registered User
Join Date
Oct 2013
Posts
4

Unanswered: Detecting joined records that number only 1

I was wondering if someone could help with a bit of basic sql.

I have 2 tables:
Table 1 Customers with column CustomerId
Table 2 Purchases with columns PurchaseId and CustomerId

I want to select all customers who have made just 1 purchase, what would be the sql for that?

I know I can join the tables to get the customers with purchases, but I don't know how to limit it to those with just 1 purchase (and exclude all the other customers) - any help appreciated.

Thanks.

2. Registered User
Join Date
Nov 2004
Posts
1,428
Hi,

Break up the problem in smaller steps that you can solve, build further on the previous results to get the final result.

#1 count number of purchases per client (columns in the result CustomerId, CountPurchases; you only need the Purchases table)
#2 based on the previous result #1, select those clients with only 1 purchase (CountPurchases = 1)
#3 join the clients with only one purchase (step #2) with the Customers table.

We don't solve homework assignments on this forum, but we will help you when you show you did the effort to try to solve it and got stuck.

3. Registered User
Join Date
Oct 2013
Posts
4
Thanks for the reply, but I'm a bit confused by it, this is what I have so far:

#1 SELECT COUNT(PurchaseId) CountPurchases FROM Purchases GROUP BY CustomerId

-this gives me the number of purchases per customer, is that what you're suggesting?

#2 Based on #1 how do I select the CountPurchases of 1? I think maybe just a simple Where CountPurchases=1 but I haven't quite figured it out!?! So far I have (but the syntax is wrong)

SELECT COUNT(PurchaseId) CountPurchases FROM Purchases GROUP BY CustomerId WHERE (CountPurchases=1)

#3 As for this step I think I could join the result of #2 to the customer table, when I get #2 figured out. What am I doing wrong on #2

Thanks.

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Check out the HAVING clause for #2. The HAVING clause behaves a lot like the WHERE clause does, but after the GROUP BY is applied.

-PatP

5. Registered User
Join Date
Oct 2013
Posts
4
Ok thanks for the hint.

For #2 I now have:

SELECT COUNT(PurchaseId) As CountPurchases, CustomerId FROM Purchases GROUP BY CustomerId HAVING (COUNT(PurchaseId)=1)

This seems to give me the correct customer. Just a quick question I tried to use
...GROUP BY CustomerId HAVING (CountPurchases=1)
but the alias CountPurchases resulted in a syntax error, can the alias not be used?

So now for #3 I'm afraid I seem to be making a bit of a mess of it, I thought it would be easier. I realise I need to link up the result of #2 with a join of table Customers but I'm not sure how, I have as follows, but it's clearly wrong!?!

Select COUNT(PurchaseId) As CountPurchases, Customers.CustomerId, Purchases.PurchaseId From Customers
Left Join Purchases On (Customers.CustomerId = Purchase.CustomerId)
Group By Customer.CustomerId Having (COUNT(PurchaseId)=1)

Thanks.

6. Registered User
Join Date
Jan 2013
Posts
359
CREATE TABLE Customers
(customer_id CHAR(10) NOT NULL PRIMARY KEY,
.. );

CREATE TABLE Purchases
(purchase_nbr CHAR(15) NOT NULL PRIMARY KEY,
customer_id CHAR(10) NOT NULL
REFERENCES Customers (customer_id),
.. );

SELECT customer_id
FROM Purchases
GROUP BY customer_id
HAVING COUNT(*) = 1;

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by AtlanticBase
but the alias CountPurchases resulted in a syntax error, can the alias not be used?
No, the alias can't be used. If you follow the discussion in the "Logical Processing Order of the SELECT statement" section at SELECT (Transact-SQL) you will see that the SELECT operation (materializing the values of the columns and assigning them aliases) happens after the HAVING clause is executed.

There are several distinctly different ways to proceed from this point, but without knowing what you've been taught so far we'll need to wait for you to take the next step.

-PatP

8. Registered User
Join Date
Nov 2004
Posts
1,428
You can use the result set of one query as a replacement for a table in a SELECT statement. Here you see how the Customers table is JOINed with the result set of step #2, the result set was given the name "CustomersWithOnePurchase".
Code:
```SELECT Customers.id, Customers.name, Customers.LastName
FROM Customers
INNER JOIN (SELECT customer_id, COUNT(*) as CountPurchases
FROM Purchases
GROUP BY customer_id
HAVING COUNT(*) = 1
) as CustomersWithOnePurchase ON
Customers.id = CustomersWithOnePurchase.customer_id```
Do pay attention to a seemingly small difference between what you wrote and what Celko wrote:
(1) HAVING COUNT(PurchaseId)=1
(2) HAVING COUNT(*) =1

In (1) you count the number of records where the column PurchaseId has a value (IS NOT NULL).
In (2) you count the number of records.

What we need here is the number of records, what (2) returns.

In this particular case, both will give the same result, as PurchaseId is a NOT NULL column (the Primary key).
If you had taken another column, a not mandatory one, this would have given a (hard to find) bug.

I seldom need (1), in the majority of cases I must use (2).

9. Registered User
Join Date
Oct 2013
Posts
4
Many Thanks to you all.

I particularly appreciate the explanations

10. Registered User
Join Date
Nov 2004
Posts
1,428
Just a quick question I tried to use
...GROUP BY CustomerId HAVING (CountPurchases=1)
but the alias CountPurchases resulted in a syntax error, can the alias not be used?
It can be used, but not within the SELECT statement. For the reason, I refer to Pat's post. Compare both SQL scripts, they will return the same result set. In the second version, the alias is used to refer to the COUNT(*) column of the subselect PurchasesPerCustomer.
Code:
```SELECT Customers.id, Customers.name, Customers.LastName
FROM Customers
INNER JOIN (SELECT customer_id, COUNT(*) as CountPurchases
FROM Purchases
GROUP BY customer_id
HAVING COUNT(*) = 1
) as CustomersWithOnePurchase ON
Customers.id = CustomersWithOnePurchase.customer_id```
Is semantically the same as:
Code:
```SELECT Customers.id, Customers.name, Customers.LastName
FROM Customers
INNER JOIN (SELECT customer_id, COUNT(*) as CountPurchases
FROM Purchases
GROUP BY customer_id
) as PurchasesPerCustomer ON
Customers.id = PurchasesPerCustomer.customer_id
WHERE PurchasesPerCustomer.CountPurchases = 1```
The first will be more efficient, I think.
Last edited by Wim; 10-01-13 at 19:28.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•