Need help in writing SQL query

Question

Table - Products has number of products as below

╔═══════════╦═════════════╗
║ PRODUCTID ║ PRODUCTNAME ║
╠═══════════╬═════════════╣
║         1 ║ Nokia       ║
║         2 ║ Samsung     ║
║         3 ║ BlackBerry  ║
╚═══════════╩═════════════╝

Table - SalesPersonProduct has the below records

╔═══════════════╦═══════════╗
║ SALESPERSONID ║ PRODUCTID ║
╠═══════════════╬═══════════╣
║ S1            ║         1 ║
║ S1            ║         2 ║
║ S1            ║         3 ║
║ S2            ║         1 ║
║ S3            ║         2 ║
╚═══════════════╩═══════════╝

Write a SQL query that returns the total number of sold products?

Answer 1

This should be pretty straight forward. You need first to join both tables. The query below uses LEFT JOIN because it includes all the records on the lefthand side table which is Products even if it has a matching record or none in the righthand side table which is SalesPersonProduct.

After the records has been joined, you can now use COUNT() which is an aggregate function to count the number of records for each group.

Since the query is using LEFT JOIN, all the records that has no matching record on table SalesPersonProduct will have a value of zero on the column TotalSold.

SELECT  a.ProductID,
        a.ProductName,
        COUNT(b.ProductID) TotalSold
FROM    Products a
        LEFT JOIN SalesPersonProduct b
            ON a.ProductID = b.ProductID
GROUP   BY  a.ProductID,
            a.ProductName

• SQLFiddle Demo

OUTPUT

╔═══════════╦═════════════╦═══════════╗
║ PRODUCTID ║ PRODUCTNAME ║ TOTALSOLD ║
╠═══════════╬═════════════╬═══════════╣
║         1 ║ Nokia       ║         2 ║
║         2 ║ Samsung     ║         2 ║
║         3 ║ BlackBerry  ║         1 ║
╚═══════════╩═════════════╩═══════════╝

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

Answer 2

If you just want to return the total number fo sales, then you can easily do this by counting the rows in the SalesPersonProduct table:

select count(productid) TotalProducts
from SalesPersonProduct;

But if you want the total number of sales for each product, then you will need to JOIN the tables on the productId column:

select p.productname, count(s.productid) TotalSales
from products p
left join SalesPersonProduct s
  on p.productid = s.productid
group by p.productname

See SQL Fiddle with Demo.

In the JOIN version, I close a LEFT JOIN which will return all product names, even if there are no sales for it. In your sample data, if you added Apple as a product name, then you would return the following result with a LEFT JOIN:

| PRODUCTNAME | TOTALSALES |
----------------------------
|       Apple |          0 |
|  BlackBerry |          1 |
|       Nokia |          2 |
|     Samsung |          2 |