CODEWITHSUNDEEP
mysqlsqlunion
Rick1990
Rick1990

Reputation: 95

Possibly advanced SQL query UNION of 3 Tables

Goal. To make a query that displays Product Name(Products), Product Type(ProductTypes), and total number of sales of each product(Sales)

Here are my tables: enter image description here

I am having real difficulty figuring out how I am meant to do this. I am trying to do a UNION and a few other things but cannot get it to work. I can get the total number of sales by using this SELECT ProductID, count(*) as NumSales from Sales group by ProductID but really struggling to do the rest and format it correctly. Any help would be appreciated.

EDIT: Select Products.ProductName, ProductTypes.ProductType From Products INNER JOIN ProductTypes ON Products.ProductTypeID=ProductTypes.ProductTypeID I have this to display this right now, just need to join the sales count somehow.

enter image description here

Upvotes: 0

Views: 197

Answers (1)

areklipno
areklipno

Reputation: 528

try:

select prod.ProductName, ptyp.ProductType, count(SaleID) count_sale
from Products prod
join ProductTypes ptyp on ( ptyp.ProductTypeID= prod.ProductTypeID)
join Sales sal on ( sal.ProductID = prod.ProductID)

group by prod.ProductName, ptyp.ProductType

Upvotes: 1

Related Questions