SQL Server - SELECT statement returning null

Question

The following SELECT statement results this

SELECT ProductID, SUM(Quantity) AS Quantity
FROM Production.ProductInventory
WHERE ProductID NOT IN (1001)
GROUP BY ProductID;

How come the edited SELECT statement below does not produce anything?

SELECT ProductID, SUM(Quantity) AS Quantity
FROM Production.ProductInventory
WHERE Quantity >= 1800
GROUP BY ProductID;

Are you serious? None of the sums that you have shown exceed 1800. If the value is always non-negative, then I wouldn't expect such large values. — Gordon Linoff
– Gordon Linoff, Commented Sep 24, 2018 at 16:19
@GordonLinoff There are sums larger than 1800 just can't see in the image as the list goes down a lot — Dane Cameron
– Dane Cameron, Commented Sep 24, 2018 at 16:19

Lukasz Szozda · Accepted Answer · 2018-09-24 16:17:07Z

6

You added WHERE clause:

SELECT ProductID, SUM(Quantity) AS Quantity
FROM Production.ProductInventory
WHERE Quantity >= 1800  -- no single row with Quantity that is higher than 1800
GROUP BY ProductID;

You probably want to filter values after aggregation:

SELECT ProductID, SUM(Quantity) AS Quantity
FROM Production.ProductInventory
GROUP BY ProductID
HAVING SUM(Quantity) >= 1800;   -- HAVING instead of WHERE

Filtering:

WHERE - before aggregation
HAVING - after aggregation

answered Sep 24, 2018 at 16:17

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-09-24 16:20:35Z

0

The WHERE is before aggregation, so it filters out all rows (apparently). You want HAVING. But, fix the aliases, so they are not confusing:

SELECT ProductID, SUM(Quantity) AS total_Quantity
FROM Production.ProductInventory
GROUP BY ProductID
HAVING total_Quantity >= 1800;

Or, repeat the expression:

SELECT ProductID, SUM(Quantity) AS total_Quantity
FROM Production.ProductInventory
GROUP BY ProductID
HAVING SUM(Quantity) >= 1800;

answered Sep 24, 2018 at 16:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Gordon Linoff Over a year ago

@RyanWilson . . . As a point in fact, Lucasz had not (from what I could tell) adding the having portion of his answer when I first answered this. It is trickier to find edit histories for the first five minutes, but his answer continued to use WHERE.

Ryan Wilson Over a year ago

@GordonLinoff If I missed that, then I apologize. I remove my down vote. Well, I tried to remove my down vote. No luck. I'll see if a moderator can help.

Collectives™ on Stack Overflow

SQL Server - SELECT statement returning null

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related