SQL using simple Subquery using 2 Tables "Product" and "ProductDeliveryTBL".
Below is shown the "Product" table:
| Prd001 |
DVD Player |
23 |
85.50 |
| Prd002 |
Mobile Phone |
45 |
70.33 |
| Prd003 |
Laptop |
15 |
250.45 |
| Prd004 |
Watch |
42 |
33.45 |
| Prd005 |
Camera |
34 |
95.75 |
Below is shown the "ProductDeliveryTBL" table:
| Aria Cruz |
Prd003 |
3/21/2011 12:00:00 AM |
| Elizabeth Brown |
Prd002 |
9/23/2011 12:00:00 AM |
| Maria Larsson |
Prd005 |
9/12/2010 12:00:00 AM |
| Martín Sommer |
Prd001 |
7/27/2009 12:00:00 AM |
| Peter Franken |
Prd002 |
9/3/2010 12:00:00 AM |
| Thomas Hardy |
Prd005 |
6/13/2009 12:00:00 AM |
| Yang Wang |
Prd005 |
8/21/2011 12:00:00 AM |
In the "Product" table different products are listed and in the "ProductDeliveryTBL", delivery status and the buyer names are listed.
In the "ProductDeliveryTBL" table ProductID is the Foreign Key.
In the "Product" table ProductID is the Primary Key.
SQL Query will retrieve the product details and the quantity sold.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select tbl.ProductID, tbl.ProdDescription, Count(tbl.ProductID) as NumberSold
From
(
Select PD.ProductID,
(Select ProductDesc From Product where ProductID = PD.ProductID) as ProdDescription
From ProductDeliveryTBL as PD
) as tbl
GROUP BY tbl.ProductID, tbl.ProdDescription
ORDER BY tbl.ProductID;
Note: Two SUBQUERIES are used, one SUBQUERY fetches the product description and the other is used in the From clause.
The SUBQUERY in the From clause acts as a table having an alias name "tbl".