Count on join table

Question

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

Answer 1

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

Answer 2

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