CODEWITHSUNDEEP
sqloracle-database
user10663015
user10663015

Reputation:

SQL/OracleDB SUM with empty values of second table

I have two tables: articles (articleID, articleName): All articles (example: 5000 pieces) sales: (salesID,articleID,salesQty,salesDate): All sales for an article

Now I need a list of all articles, including the sum of the sales. If there are no sales, 0 should be displayed. The whole should be in a certain time frame.

I have tried the following, but I get only 1000 items and no 0 values.

I have tried the following two queries:

SELECT
    article.articleName,
    SUM(NVL(sales.salesQty, 0)) AS qty
FROM article article
LEFT JOIN sales ON (article.articleID=sales.articleID)
WHERE sales.salesDate >= '2020-01-01'
GROUP BY article.articleName, sales.salesQty

SELECT
    article.articleName,
    SUM(sales.salesQty) AS qty
FROM article article
LEFT JOIN sales ON (article.articleID=sales.articleID)
WHERE sales.salesDate >= '2020-01-01'
GROUP BY article.articleName, sales.salesQty

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need to move the filtering condition to the ON clause:

SELECT a.articleName, COALESCE(SUM(s.salesQty), 0) AS qty
FROM article a LEFT JOIN
     sales s
     ON s.articleID = a.articleID AND
        s.salesDate >= '2020-01-01'
GROUP BY a.articleName;

Note other changes:

  • Table aliases that are abbreviation for the table names are easier to write and read.
  • The filter on date is in the ON clause.
  • The GROUP BY should only be on the articleName.
  • The COALESCE() is needed after the SUM() not before -- no need to do the check for every row, just for the final result.
  • COALESCE() is the standard SQL function, so is preferred over NVL().

Upvotes: 2

Related Questions