SQL Include zero rows in query

Question

I have a derived a table with a category, subcategory, date, time, and value columns (from a much larger table).

The dates are all dates with a particular property (sometimes last 5 weeks, sometimes last 5 Sundays, sometimes month to day), as with the times (sometimes midnight to now every hour, sometimes last 30 minutes every 2 minutes).

The category and subcategory are uninteresting, but if they have no entry in the value field for the time parameters they're completely ignored (which is fine).

When there is no value which matches the combination of the other columns, the row is completely ignored.

My problem is that I need the 0's included. Is there some way I can fill in 0's to the rest of the table?

My thought was to do a cross join of the table against itself using only the first 4 columns, then left join with isnull() on the value column. But the cross join kept including the 5th column.

Your question is a little hard to follow: "they're completely ignored (which is fine)" versus "My problem is that I need the 0's included". Which is it? Included or ignored? Sample data and desired results would really help. — Gordon Linoff
– Gordon Linoff, Commented Aug 15, 2014 at 15:50

Gordon Linoff · Accepted Answer · 2014-08-15 15:53:10Z

1

If I understand correctly, you want all combinations of date, time, category, and subcategory that your query returns. If so, you can use cross join to get the combinations and then left outer join to fill in the values or 0:

with t as (
      <your query here>
     )
select d.date, ti.time, sc.category, sc.subcategory, coalesce(t.value, 0) as value
from (select distinct date from t) d cross join
     (select distinct time from t) ti cross join
     (select distinct category, subcategory from t) sc left outer join
     t
     on t.date = d.date and t.time = ti.time and
        t.category = sc.category and t.subcategory = sc.subcategory;

answered Aug 15, 2014 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user38858 Over a year ago

Thank you. I'll try that out, and fill in more details when I can.

user38858 Over a year ago

It works, but it takes about a minute. I'm trying to figure out how to get the same functionality without the time issue.

Gordon Linoff Over a year ago

@user38858 . . . If you can put your query into a temporary table or table variable, that will probably speed the query.

user38858 Over a year ago

That's exactly what I did. When I crossed the time stuff with the category stuff, it would always take forever; until I put the category stuff in a temp table. I have no idea why, but it seems to work.

Gordon Linoff Over a year ago

@user38858 . . . You could try building three indexes on the table, one for date, one for time, and one for category, subcategory for performance.

Collectives™ on Stack Overflow

SQL Include zero rows in query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related