Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    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. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    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. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    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. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by AtlanticBase View Post
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Oct 2013
    Posts
    4

    Thumbs up

    Many Thanks to you all.

    I particularly appreciate the explanations

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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