Confusion between HAVING and WHERE, SQL Server

Question

I am trying to fetch all vendors whose product's quantity (SUM) is equal to zero. This is what I have done so far but it doesn't return any row nor it gives any error. I am making sure there are products whose quantity is zero and script is running on proper database.

select 
    VI.Name, Cp.ProductName
from
    VendorInfo VI 
inner join 
    VendorTrading VT on VI.Id = VT.VendorId 
inner join 
    CustomerProducts CP on VT.Id = CP.VendorTradingId
group by 
    VI.Name, CP.ProductName
having
    sum(CP.ProductQuantity) = 0

I am confused about HAVING and WHERE. What do I do?

UPDATE:

This is the VendorInfo table:

This is the VendorTrading table:

This is the CustomerProduct table:

Now expected result result should be like:

There will be multiple rows of same product but with different Tradedate, So it should first SUM all Product Quantity and if it equals to zero then it returns VendorName and Product name that it has 0 Product quantity in certain period of time. later I also need total count of Vendor whose Product Quantity is 0. Hope it clears everything

You should provide sample data and desired results (and perhaps a SQL Fiddle). Your description simply sounds like no vendor/product combinations meet the conditions; and that seems totally reasonable. — Gordon Linoff, Feb 28 '16 at 15:22
Maybe you need to determine `WHERE` for `TradeDate`? For example `WHERE TradeDate = '2015-12-25'` — gofr1, Feb 28 '16 at 16:51
You apply a `WHERE` clause, if you want to have a condition with a column and a fixed value, e.g. `WHERE VT.Id = 1`. You use `HAVING` if you want to have a condition with an **aggregate**, e.g. with a `MAX`, `MIN`, `SUM`, `COUNT` or `AVG` - as you correctly did. — marc_s, Feb 28 '16 at 16:56

score 0 · Answer 1 · answered Feb 28 '16 at 15:57

0

I believe your problem is this: if the Product Quantity of a vendor is zero, there will be no rows in the Trading table for that vendor, and none in the result of the inner join either. You need to do something like:

select VI.Name, Cp.ProductName
FROM VendorInfo VI 
LEFT OUTER join VendorTrading VT  
on VI.Id = VT.VendorId 
LEFT OUTER join CustomerProducts CP 
on VT.Id = CP.VendorTradingId
GROUP BY VI.Name, CP.ProductName
HAVING  SUM( CP.ProductQuantity ) = 0

then what you are doing with the HAVING clause is correct.

answered Feb 28 '16 at 15:57

Hellmar Becker

2,824
12
18

No, If a product quantity is 0. It is not necessary that there will no rows on Trading table. One Trade will contain Many product. Some of which may contain some quantity some may be equal to 0. – Affan Shahab Feb 28 '16 at 16:04
Thanks for your effort so far @Hellmar Becker. I tried your query but it doesn't return anything – Affan Shahab Feb 28 '16 at 16:06

gofr1 · Answer 2 · 2016-02-28T17:18:31.530

I think you should add WHERE TradeDate = 'datehere', because with your sample:

WITH VendorInfo AS (
SELECT  2 as Id,
        100 as Code,
        'Yousuf M/s' as Name
),
VendorTrading AS (
SELECT  1 as Id,
        2 as VendorId,
        '2015-12-25' as TradeDate
),
CustomerProducts AS (
SELECT * FROM (VALUES
(1, 1, 'ULTRA', 0),
(2, 1, 'EXTASY', 5),
(3, 1, 'XXL', 5),
(4, 1, 'KPT', 5),
(5, 1, 'ORANGE', 5),
(6, 1, 'STRAWBERRY', 5),
(7, 1, 'PERFORM', 5),
(8, 1, 'INTENSE', 5),
(9, 1, 'SENSUAL', 5),
(10, 2, 'ULTRA', 0),
(11, 2, 'EXTASY', 5),
(12, 2, 'XXL', 5),
(13, 2, 'KPT', 5),
(14, 2, 'ORANGE', 5),
(15, 2, 'STRAWBERRY', 5),
(16, 2, 'PERFORM', 5),
(17, 2, 'INTENSE', 5),
(18, 2, 'SENSUAL', 5) 
)as cp(Id,VendorTradingId,ProductName,ProductQuantity)
)

select VI.Name, Cp.ProductName
FROM VendorInfo VI 
inner join VendorTrading VT 
on VI.Id = VT.VendorId 
inner join CustomerProducts CP 
on VT.Id = CP.VendorTradingId
GROUP BY VI.Name, CP.ProductName
HAVING  SUM( CP.ProductQuantity ) = 0

The result is:

Name       ProductName
---------- -----------
Yousuf M/s ULTRA

(1 row(s) affected)

So it can be more strings with ULTRA with more then zero ProductQuantity on another dates.

score 0 · Answer 3 · edited May 23 '17 at 12:13

0

Thanks every one for your efforts, I finally did it this way.

select  Count(*) From 
(select distinct VI.Name, Cp.ProductName
FROM VendorInfo VI inner join VendorTrading VT on VI.Id = VT.VendorId inner join CustomerProducts CP on VT.Id = CP.VendorTradingId
Where VT.Tradedate = '2015-12-25' and CP.ProductName = 'ULTRA'
GROUP BY VI.Name, Cp.ProductName, CP.ProductQuantity
HAVING  COUNT( CP.ProductQuantity ) = 0) as x

but here is another problem, I posted it as new question. see here. Thanks

edited May 23 '17 at 12:13

Community

1
1

answered Feb 28 '16 at 18:30

Affan Shahab

838
3
17
39

This query returns a simple number whereas your query in the question returns a list of vendor names. Therefore this query does not seem like an answer to the question you have asked. Is the real solution here the subquery aliased as 'x'? If so you should edit your answer and mark it as the correct one. – Stuart J Cuthbertson Apr 11 '16 at 12:53

Confusion between HAVING and WHERE, SQL Server

3 Answers3

Linked