StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The Case Statement is my best sql friend. You also need a table for time to generate your 0 rev in both months.</p> <p>Assumptions are based on the availability of following tables:</p> <blockquote> <p>sales: Category | Revenue | Yearh | Month</p> </blockquote> <p>and</p> <blockquote> <p>tm: Year | Month (populated with all dates required for reporting)</p> </blockquote> <p>Example 1 without empty rows:</p> <pre><code>select Category ,month ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year from sales where year in (2008,2007) group by Category ,month </code></pre> <p>RETURNS:</p> <pre><code>Category | Month | Rev. This Year | Rev. Last Year Bikes 1 10 000 0 Bikes 2 12 000 11 000 Bikes 3 12 000 11 500 Bikes 4 0 15 400 </code></pre> <p>Example 2 with empty rows: I am going to use a sub query (but others may not) and will return an empty row for every product and year month combo.</p> <pre><code>select fill.Category ,fill.month ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year from sales Right join (select distinct --try out left, right and cross joins to test results. product ,year ,month from sales --this ideally would be from a products table cross join tm where year in (2008,2007)) fill where fill.year in (2008,2007) group by fill.Category ,fill.month </code></pre> <p>RETURNS:</p> <pre><code>Category | Month | Rev. This Year | Rev. Last Year Bikes 1 10 000 0 Bikes 2 12 000 11 000 Bikes 3 12 000 11 500 Bikes 4 0 15 400 Bikes 5 0 0 Bikes 6 0 0 Bikes 7 0 0 Bikes 8 0 0 </code></pre> <p>Note that most reporting tools will do this crosstab or matrix functionality, and now that i think of it SQL Server 2005 has pivot syntax that will do this as well.</p> <p>Here are some additional resources. CASE <a href="http://www.4guysfromrolla.com/webtech/102704-1.shtml" rel="nofollow noreferrer">http://www.4guysfromrolla.com/webtech/102704-1.shtml</a> SQL SERVER 2005 PIVOT <a href="http://msdn.microsoft.com/en-us/library/ms177410.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms177410.aspx</a></p>

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