CODEWITHSUNDEEP
sqloracle-database
GrouchyPanda
GrouchyPanda

Reputation: 1084

How to include missing rows in sql return

I am currently trying to do a query like this:

(Psuedocode)

SELECT 
  NAME, SUM(VALUE), MONTH 
FROM TABLE 
WHERE MONTH BETWEEN 12 MONTHS AGO AND NOW 
GROUP BY MONTH, NAME

The problem I am getting is that a name exists in a few of the months, but not all of the months, so if i filter this down to return the values for only one name, i sometimes get only 3 or 4 rows, rather than the 12 I expect to see.

My question is, is there a way to return rows, where it will still include the name, and month within the range, where the value would just be set to zero when I am missing the row from the previous result.

My first thought was to just union another select onto it, but I cant seem to get the logic to work to adhere to the group by, as well as the where clauses for limiting the names.

Upvotes: 2

Views: 1260

Answers (3)

GrouchyPanda
GrouchyPanda

Reputation: 1084

I ended up implementing a left outer join similar to @paqogomez 's comment. As my team is already maintaining a time table, its very easy to get the month list for an outer join. 

SELECT NAME, SUM(VALUE), TIME.MONTH
FROM (SELECT DISTINCT MONTH FROM TIME_TABLE 
      WHERE MONTH BETWEEN 12 MONTHS AGO AND NOW) TIME
     LEFT OUTER JOIN TABLE ON (TIME.MONTH = TABLE.MONTH)
GROUP BY TIME.MONTH, NAME

Upvotes: 0

xionutz2k
xionutz2k

Reputation: 729

you can use something like the following table to generate all the past 12 months as separate rows:

SELECT add_months(trunc(add_months(sysdate, -12), 'MONTH'), LEVEL - 1)   AS month_in_range
FROM  all_objects
CONNECT BY  LEVEL <= 1 + months_between(add_months(sysdate, -12), TRUNC (sysdate, 'MONTH'));

and then do an outer join between you table and this.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I you have data for all months, you can take the following approach. Generate all the rows (uses a cross join) then bring in the data you want:

select  m.month, n.name, sum(t.value)
from (select distinct month from table) m cross join
     (select distinct name from table) n left join
     table t
     on t.month = m.month and t.name = n.name
group by m.month, n.name;

This will return the missing sums as NULL values. If you want zero, then use coalesce(sum(t.value), 0).

Upvotes: 3

Related Questions