CODEWITHSUNDEEP
sql-server
Jprada
Jprada

Reputation: 97

Obtain null values of result from a query

I have this query:

SELECT U.LOCATION,SUM(S.EXISTENCIA) AS Total
FROM LOCATIONS U
LEFT JOIN CANT_WAREHOUSE S ON U.LOCATION= S.LOCATION
WHERE U.ENABLED= 1 
GROUP BY U.LOCATION

And return data like this:

LOCATION  | Total
2A-1      | 566.0000000
2A-2      | 14.0000000
2A-3      | 1.0000000
2A-4      | (null)
2A-5      | 1.0000000
2A-6      | (null)
2A-7      | 6.0000000
2B-1      | 560.0000000
2B-2      | (null)

Is there a way to get the 'LOCATION' with 'TOTAL' = null using subquery or something like that?

Upvotes: 1

Views: 47

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

Use the HAVING Clause

SELECT U.LOCATION,SUM(S.EXISTENCIA) AS Total 
FROM LOCATIONS U
LEFT JOIN CANT_WAREHOUSE S ON U.LOCATION= S.LOCATION
  and U.ENABLED= 1 
GROUP BY U.LOCATION
HAVING SUM(S.EXISTENCIA) IS NULL

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

Use NOT EXISTS

SELECT U.LOCATION,
       NULL AS Total
FROM   LOCATIONS U
WHERE  NOT EXISTS (SELECT 1
                   FROM   CANT_WAREHOUSE S
                   WHERE  U.LOCATION = S.LOCATION)
       AND U.ENABLED = 1
GROUP  BY U.LOCATION

Upvotes: 1

Related Questions