CODEWITHSUNDEEP
sql
NewbieProgrammer
NewbieProgrammer

Reputation: 864

COUNT on Sub Query and Join

Guys I am trying to use COUNT on two different SQL statements. One is using joins and the other one is using sub queries. But the problem is, both are displaying different number of rows. Why is that?

Query 1 :

SELECT  COUNT(*) AS 'Count',
        (SELECT c.CustomerName 
         FROM dbo.tblCustomer c 
         WHERE o.CustomerID = c.CustomerID) AS 'CustomerName',
        (SELECT ProductName 
         FROM dbo.tblProduct p 
         WHERE o.ProductID = p.ProductID) AS 'ProductName'
FROM dbo.tblOrder o
WHERE (o.OrderDate BETWEEN '2014-5-15' AND '2014-5-20')
GROUP BY o.CustomerID, o.ProductID

Query 2 :

SELECT  COUNT(*) AS 'Count', 
        dbo.tblCustomer.CustomerName, 
        dbo.tblProduct.ProductName
FROM dbo.tblCustomer 
INNER JOIN dbo.tblOrder ON dbo.tblCustomer.CustomerID = dbo.tblOrder.CustomerID 
INNER JOIN dbo.tblProduct ON dbo.tblOrder.ProductID = dbo.tblProduct.ProductID
WHERE (dbo.tblOrder.OrderDate BETWEEN '2014-5-15' AND '2014-5-20')
GROUP BY CustomerName,ProductName

Both queries are same in nature except one is using JOINs while the other one is using sub queries. Then why am I getting different number of rows in return?

Upvotes: 1

Views: 95

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

In the first query you group by ids, in the second by names. So the first query gives you counts per customer and product, whereas the second query gives you counts per equally named customers and equally named products.

Example:

user 1 = John, user 2 = John
product a = toy, product b = toy
orders: 1 a, 1 a, 1 b, 2 a

query 1:

2, John, toy
1, John, toy
1, John, toy

query 2:

4, John, toy

Upvotes: 1

Related Questions