Sql Query to present NULL rows

34
February 12, 2019, at 08:40 AM

I have a simples 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 wich to have a query that will return the sells of every vender, for especific 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 curent 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 diferent aproches with ISNULL and COALESC with no luck. Any help is welcome.

Thank you.

Answer 1

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.
Answer 2

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
READ ALSO
Logging in in PHP while logging data is AES encrypted

Logging in in PHP while logging data is AES encrypted

My SQL table contains two types of data used to log inFirst is email(In table named standard_name) and second is password

44
How to setup a Linux development server using Docker to support multiple versions of PHP and MySQL

How to setup a Linux development server using Docker to support multiple versions of PHP and MySQL

I have many PHP projects, and each project requires different PHP and MySQL versionsI want to setup a Docker machine that runs multiple instances of the server with specific versions of PHP and MySQL

22
How to get unique records with join tables and without using group by

How to get unique records with join tables and without using group by

I want to get unique record from 2 tables without using Group By instead of i want to use DISTINCT because when i used group by it does not return last updated records

18