Exclude a row if a sum(x) used in constructing it returns null-CodePudding

I'm trying to manipulate a database of artworks and the collections they're stored in. What I want is to display the name of all the collections, the number of artworks in them and the sum value of all the artworks' prices, with the caveat that if the sum of the prices returns as null then I want to exclude that collection from the print. Some artworks have prices listed and some have them set to null.

Here's my code, I haven't been able to figure out a way to execute that caveat without introducing unintended behavior.

SELECT COLLECTION.NAME, COUNT(ARTWORK.ARTWORKID), SUM(ARTWORK.PRICE)
FROM ARTWORK JOIN COLLECTION ON ARTWORK.COLLECTIONID = COLLECTION.COLLECTIONID
WHERE SUM(PRICE) IS NOT NULL
GROUP BY COLLECTION.NAME
ORDER BY COLLECTION.NAME;

I am aware the third line introduces an error, it's the best I could do to showcase my thought process. If you exclude the third line, this returns the expected output except with some collections with a null sum value.

CodePudding user response：

First thing, take note that sum() returns the sum of not-NUUL values in the group, so it will return NULL only if all prices of the collections are NULL. If one artwork has a null price but other artworks of the same collection have not-NULL values, sum() will not be null.

If this is your desired result, the you just have to use the HAVING clause instead of WHERE:

SELECT COLLECTION.NAME, COUNT(ARTWORK.ARTWORKID), SUM(ARTWORK.PRICE)
FROM ARTWORK JOIN COLLECTION ON ARTWORK.COLLECTIONID = COLLECTION.COLLECTIONID
GROUP BY COLLECTION.NAME
HAVING SUM(PRICE) IS NOT NULL
ORDER BY COLLECTION.NAME;

If instead you want to exclude collections having at least a NULL priced artwork:

SELECT COLLECTION.NAME, COUNT(ARTWORK.ARTWORKID), SUM(ARTWORK.PRICE)
FROM ARTWORK JOIN COLLECTION ON ARTWORK.COLLECTIONID = COLLECTION.COLLECTIONID
GROUP BY COLLECTION.NAME
HAVING COUNT(PRICE) = COUNT(*)
ORDER BY COLLECTION.NAME;

COUNT(PRICE) counts the number of non-NULL prices, while COUNT(*) counts the total number of rows in the collection, so they will be equal only if all prices are not null.