CODEWITHSUNDEEP
mysqlsql
Vicente Chazard
Vicente Chazard

Reputation: 47

SQL Query to present NULL rows

I have a simple table:

Table: pn
id, vender, article, sells, date
01, Peter, bread, 10, 02-01-2019
02, Tom, bread, 3, 10-01-2019
03, Paul, water, 3, 05-01-2019
04, Peter, oil, 5, 16-01-2019
05, Paul, bread, 1, 22-01-2019
06, Tom, oil, 2, 30-01-2019
07, Peter, bread, 1, 27-01-2019
08, Tom, oil, 1, 17-01-2019

I wish to have a query that will return the sells of every vender, for specific articles, for a specific period of time. Ex: Bread and oil, between 01-01-2019 and 31-01-2019. The query result should be:

Vendor, Article, Sells
Paul, bread, 1
Paul, oil, 0
Peter, bread, 11
Peter, oil, 5
Tom, bread, 3
Tom, oil, 3

My problem is since some Vendors have no sells for specific articles, the rows for that Vendor/Article are not printed, like so:

Vendor, Article, Sells
Paul, bread, 1
Peter, bread, 11
Peter, oil, 5
Tom, bread, 3
Tom, oil, 3

Notice the missing "Paul, oil, 0"

My current code looks like this:

SELECT pn.vendor, pn.article, SUM (pn.sells)
FROM pn
WHERE pn.date BETWEEN  01.01.2019 AND 31.01.2019
   AND (pn.article = 'bread' OR pn.article = 'oil')   
GROUP BY pn.vendor, pn.article
ORDER BY pn.vendor ASC, pn.article ASC

I have tried many different approaches with ISNULL and COALESC with no luck. Any help is welcome.

Thank you.

Upvotes: 0

Views: 55

Answers (2)

jarlh
jarlh

Reputation: 44786

Skip the WHERE clause to get Paul too. Then use a case expression to do conditional aggregation:

SELECT pn.vendor, pn.article,
        SUM (case when pn.date BETWEEN  01.01.2019 AND 31.01.2019
                       and AND (pn.article = 'bread' OR pn.article = 'oil')  then pn.sells
                  else 0 end)
FROM pn
GROUP BY pn.vendor, pn.article
ORDER BY pn.vendor ASC, pn.familia ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use a cross join to generate the rows and then left join to bring in the values.

SELECT v.vendor, a.article, COALESCE(SUM(pn.sells), 0)
FROM (SELECT DISTINCT vendor FROM pn) v CROSS JOIN
     (SELECT DISTINCT article FROM pn) a LEFT JOIN
     pn
     ON pn.vendor = v.vendor AND
        pn.article = a.article AND
        pn.date >= '2019-01-01' AND
        pn.date < '2019-02-01' 
WHERE a.article IN ('bread', 'oil')   
GROUP BY v.vendor, a.article
ORDER BY v.vendor ASC, a.article ASC;

Notes:

  • Use standard date formats for your dates (YYYY-MM-DD is both standard in SQL and ISO 8601 standard).
  • Use table aliases qualify all column references.
  • I have no idea what familia is in the ORDER BY.
  • In most database you could just list the articles you want as a derived table for a.

Upvotes: 3

Related Questions