Thanks for your response.
I got that query working,
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
GROUP BY P.productname ;
but what if there was another column CustomerId, which exists in both tables
and I want to just see products just for a particular customer?
So if a product is offered to a customer , but he doesn't order it then in
result I want to show that product with count as 0...Try this:
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
AND P.customerid = S.customerid
WHERE P.customerid = /* specify the required customer */
GROUP BY P.productname ;
The best way to get help with a problem such as this is to post DDL and
sample data. The following artcile explains how:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thnx David,
It worked for me.
"David Portas" wrote:
> Try this:
> SELECT P.productname, COUNT(S.productname)
> FROM Products AS P
> LEFT JOIN Sales AS S
> ON P.productname = S.productname
> AND P.customerid = S.customerid
> WHERE P.customerid = /* specify the required customer */
> GROUP BY P.productname ;
> The best way to get help with a problem such as this is to post DDL and
> sample data. The following artcile explains how:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment