StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Edit 2: I think this will work:</p> <pre><code>select mr.region, mr.class_month, coalesce(a.attendance, 0) as attendance from (select * from (select distinct region as region from mbo_monthly_attendance) r, (select distinct class_month as class_month from mbo_monthly_attendance) c) mr LEFT OUTER JOIN mbo_monthly_attendance a ON mr.class_month = a.class_month AND mr.region = a.region ; </code></pre> <p>You could break it up by views to make it more understandable:</p> <pre><code>create view regions as select distinct region from mbo_monthly_attendance; create view class_months as select distinct class_month from mbo_monthly_attendance; create view region_months as select * from regions, class_months; select mr.region, mr.class_month, coalesce(a.attendance, 0) as attendance from region_months mr LEFT OUTER JOIN mbo_monthly_attendance a ON mr.class_month = a.class_month AND mr.region = a.region ; </code></pre> <hr> <p>Create a table for 'class month' and use an outer join with the monthly attendance table.</p> <pre><code>create table months (class_month date); insert into months values ('1/1/2012'); insert into months values ('2/1/2012'); insert into months values ('3/1/2012'); insert into months values ('4/1/2012'); insert into months values ('5/1/2012'); insert into months values ('6/1/2012'); SELECT a.region ,a.class_month ,COALESCE (a.attendance, 0) AS attendance FROM dbo.mbo_monthly_attendance a LEFT OUTER JOIN months m ON a.class_month = m.class_month WHERE m.class_month between @StartDate and @EndDate; </code></pre> <p>Rather than creating a separate table, you could use the attendance table to get all months present:</p> <pre><code>create view months as select distinct class_month from mbo_monthly_attendance </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload