CODEWITHSUNDEEP
mysqlsqldatetimeleft-join
LocEngineer
LocEngineer

Reputation: 2917

Query to include non-existent rows as 0 value

Although there are a number of similar questions on SO, I could not find any answer that would give me the desired output.

Situation: table "purchaseorders" containing POs with various details. Example record:

| id | orderId | number | vendorNumber | grossValue | bookingYear |
|----|---------|--------|--------------|------------|-------------|
| 14 | 691     | 1      | 12345        | 152.43     | 2016        |

I want aggregate sums(grossValue) for specific vendors for the past 5 years, including 0 values. Required output:

enter image description here

Current actual output:

enter image description here

My attempt at SQL:

SELECT po.bookingYear, IFNULL (sum(po.grossValue),0) FROM purchaseorder as po
LEFT JOIN (select 2016 as yr UNION ALL select 2017 as yr UNION ALL select 2018 as yr UNION ALL select 2019 as yr UNION ALL select 2020 as yr) as years ON po.bookingYear=years.yr
WHERE po.vendorNumber=12345 group by po.bookingYear;

I started out with a simple "WHERE bookingYear IN (...)", but since there are no records for the past three years, none are returned. Searching here and on Google suggested I left join a subquery like above (though probably not exactly like above).

I think the biggest issue here is, that I don't have NULL values for certain fields in certain years but simply no records at all for those years for some vendors.

Can anyone point me in the right direction? My SQL has become a bit rusty it seems. Speaking of: yes: mySQL.

Upvotes: 1

Views: 144

Answers (2)

GMB
GMB

Reputation: 222482

If you are running MySQL 8.0, you can use a recursive query to generate the dates. This incurs less typing than union all if you have a lot of years:

with years as (
    select 2016 bookingYear
    union all select bookingYear + 1 from years where bookingYear < 2020 
)
select y.bookingYear, coalesce(sum(po.grossValue), 0) grossValue
from years y
left join purchaseorder po on po.bookingYear = y.bookingYear and po.vendorNumber = 12345
group by y.bookingYear

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Right idea. Wrong order. The years go first:

SELECT years.yr, COALESCE(SUM(po.grossValue), 0)
FROM (select 2016 as yr UNION ALL select 2017 as yr UNION ALL select 2018 as yr UNION ALL select 2019 as yr UNION ALL select 2020 as yr
     ) years LEFT JOIN
     purchaseorder po
     ON po.bookingYear = years.yr AND
        po.vendorNumber = 12345 
GROUP BY years.yr;

Note the other changes to the query:

  • The filtering on vendorNumber is now in the ON clause rather then WHERE clause. In the WHERE clause it would turn the outer join to an inner join.
  • The GROUP BY and SELECT use years.yr. The value po.bookingYear might be NULL.
  • I prefer COALESCE(), the SQL standard function, to IFNULL() a bespoke MySQL function.

Upvotes: 2

Related Questions