CODEWITHSUNDEEP
sqlselectjoin
Archana
Archana

Reputation: 659

Need help in writing SQL query

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?

Upvotes: 3

Views: 1267

Answers (2)

John Woo
John Woo

Reputation: 263933

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

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:

Upvotes: 2

Taryn
Taryn

Reputation: 247880

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 |

Upvotes: 1

Related Questions