Reputation: 2989
ProductTable
ProductID ProductDesc
401 Hotdog
402 Ham
403 Bacon
OrderTable
OrderID OrderPayment NumOrder
5001 Cash 3
5002 Credit 2
5003 Credit 2
5004 Cash 3
OrderDetailsTable
OrderDetailsID OrderID ProductID
70001 5001 401 -
70002 5001 401 -
70003 5001 403 -
70004 5002 401
70005 5002 402
70006 5003 402
70007 5003 403
70008 5004 403 -
70009 5004 402 -
70010 5004 401 -
How I will count the ProductID on how many it was order by cash ?
Sample Output
ProductID ProductDesc CountOnCash
401 Hotdog 3
402 Ham 1
403 Bacon 2
Upvotes: 0
Views: 71
Reputation: 2629
Try This Code,
Select p.ProductID , p.ProductDesc, count(p.ProductId)
from Product p
inner join OrderDetails od on p.productid = od.productid
inner join Order1 o on o.orderid = od.orderid
where orderpayment = 'cash'
Group by p.ProductID, p.ProductDesc
I checked in sqlfiddle, check here http://sqlfiddle.com/#!3/35ec3/5/0
Upvotes: 1
Reputation: 366
Try this:
select P.ProductID, P.ProductDesc, COUNT(P.ProductId) AS CountOnCach
from Product AS P
JOIN OrderDetails AS OD ON P.ProductId = OD.ProductId
JOIN [Order] AS O ON O.OrderId = OD.OrderId
WHERE O.OrderPayment = 'Cash'
GROUP BY P.ProductID, P.ProductDesc
Upvotes: 0