CODEWITHSUNDEEP
sqloracle-database
user2926952
user2926952

Reputation: 25

Display sum of inner joined tables exluding NULL sums

SELECT 
t1.aID, t1.firstname, t1.lastname,
SUM(t2.price) AS sumsales
FROM t3
  INNER JOIN t1
    ON t3.aID = t1.aID
  INNER JOIN t2
    ON t3.wID = t2.wID
GROUP BY (t1.aID, t1.firstname, t1.lastname)
ORDER BY sumsales DESC

How can I remove all the rows with a null sumsales column from the results of this oracle SQL query?

I tried 

WHERE sumsales IS NOT NULL

above GROUP BY but it did not work.

Replacing the null with a 0 would also be acceptable but I could not figure out how to replace null during/after selection.

Thanks for your time.

Upvotes: 1

Views: 34

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can replace the NULL value with 0 using coalesce():

SELECT t1.aID, t1.firstname, t1.lastname,
       COALESCE(SUM(t2.price), 0) AS sumsales
FROM t3 INNER JOIN
     t1
     ON t3.aID = t1.aID INNER JOIN
     t2
     ON t3.wID = t2.wID
GROUP BY t1.aID, t1.firstname, t1.lastname
ORDER BY sumsales DESC;

Alternatively, if you don't want NULL values for sumsales, you can just filter out NULL prices before the aggregation:

SELECT t1.aID, t1.firstname, t1.lastname,
       SUM(t2.price AS sumsales
FROM t3 INNER JOIN
     t1
     ON t3.aID = t1.aID INNER JOIN
     t2
     ON t3.wID = t2.wID
WHERE t2.price IS NOT NULL
GROUP BY t1.aID, t1.firstname, t1.lastname
ORDER BY sumsales DESC;

This would typically be the most efficient approach. Note that you cannot use aggregation functions in a WHERE clause, but you can put them in a HAVING clause as @NirLevy suggests.

It is also unclear what the join to t3 is accomplishing, unless you have it only for filtering purposes. The code is not using any columns from that table.

Upvotes: 1

Nir Levy
Nir Levy

Reputation: 12953

Since sumsales is an aggregated value, you should use having clause:

SELECT 
t1.aID, t1.firstname, t1.lastname,
SUM(t2.price) AS sumsales
FROM t3
  INNER JOIN t1
    ON t3.aID = t1.aID
  INNER JOIN t2
    ON t3.wID = t2.wID
GROUP BY (t1.aID, t1.firstname, t1.lastname)
HAVING sumsales IS NOT NULL
ORDER BY sumsales DESC

Upvotes: 2

Related Questions