CODEWITHSUNDEEP
sql
Peter de Bruijn
Peter de Bruijn

Reputation: 822

Query that counts all items in joined table

I have three tables:

Account:

AccountId | AccountEmail

PurchasedProducts:

ProductName | AccountId | ProductTypeId

ProductTypes:

ProductTypeId | ProductTypeName

I want to make a query that counts the number of purchased products per producttype per account. It should look something like this:

AccountEmail  | ProductTypeName A | ProductTypeName B | ProductTypeName C
-------------------------------------------------------------------------
[email protected] | 0                 | 12                | 1

I can't figure out how to make this query... Can anyone help? Thanks in advance!

Upvotes: 0

Views: 44

Answers (2)

Dan Getz
Dan Getz

Reputation: 18217

The following (based on @VR46 's answer) will give the right values, but not in the exact table format the OP presented. I think, this should suffice for most uses, and in the case a producttype per column format is needed, a pivot table can be created with Excel or another query.

SELECT accountemail, producttypename, COUNT(*)
FROM   purchasedproducts p 
       INNER JOIN producttypes ot 
               ON p.producttypeid = pt.producttypeid 
       INNER JOIN account a 
               ON a.accountid = p.accountid 
GROUP  BY accountemail, producttypename

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Something like this will work

SELECT accountemail, 
       Sum(CASE 
             WHEN producttypename = 'ProductTypeName A' THEN 1 
             ELSE 0 
           END) AS 'ProductTypeName A', 
       Sum(CASE 
             WHEN producttypename = 'ProductTypeName B' THEN 1 
             ELSE 0 
           END) AS 'ProductTypeName B', 
       Sum(CASE 
             WHEN producttypename = 'ProductTypeName C' THEN 1 
             ELSE 0 
           END) AS 'ProductTypeName C' 
FROM   purchasedproducts p 
       INNER JOIN producttypes ot 
               ON p.producttypeid = pt.producttypeid 
       INNER JOIN account a 
               ON a.accountid = p.accountid 
WHERE  producttypename IN ( 'ProductTypeName A', 'ProductTypeName B', 
                            'ProductTypeName C' ) 
GROUP  BY accountemail

Upvotes: 1

Related Questions