Saturday, December 12, 2009

SQL: a Cross-Tabular Report with Case, Rollup and Grouping Functions

A Cross-Tabular Report is widely used in computer software. If you keep a journal of expenses, for instance, and by the end of year, you would like to review how much you have spent on each type of goods in a month-by-month view. Or a website administrator would like to know, for each page or URL of website, how many visitors visited using different browsers. These are cases when you need a cross-tabular report. (Definitions of Cross Tabulation or Contingency table.)

So how to generate a cross tab report in just one line of SQL? The idea is to split columns with "CASE" and group rows with "GROUP BY ROLLUP(name)" and decorate the result with "DECODE(GROUPING(name), 1, 'Total', name)".

Here's an Example:

We have a log table:

SQL> select page "Page", brws_type "Browser" from visit_table;

Page            Browser
--------------- ----------
index.htm       FF
index.htm       IE
index.htm       IE
page1.htm       FF
page2.htm       FF
page3.htm       FF
about.htm       FF
about.htm       SF
index.htm       SF
index.htm       SF
index.htm       FF

Page            Browser
--------------- ----------
page2.htm       IE
page2.htm       IE
contact.htm     IE
contact.htm     SF
page3.htm       SF

And now comes the query for cross-tabular report.

SQL> SELECT DECODE(GROUPING(page), 1, 'All pages', page) "Pages",
            COUNT(CASE WHEN brws_type='FF' THEN 1 ELSE null END) "Firefox",
            COUNT(CASE WHEN brws_type='IE' THEN 1 ELSE null END) "Internet Explorer",
            COUNT(CASE WHEN brws_type='SF' THEN 1 ELSE null END) "Safari",
            COUNT(*) count
      FROM visit_table
      WHERE 1=1
      GROUP BY ROLLUP(page)
      ORDER BY count desc;
Pages              Firefox Internet Explorer     Safari      COUNT
--------------- ---------- ----------------- ---------- ----------
All pages                6                 5          5         16
index.htm                2                 2          2          6
page2.htm                1                 2          0          3
about.htm                1                 0          1          2
contact.htm              0                 1          1          2
page3.htm                1                 0          1          2
page1.htm                1                 0          0          1

SQL is a powerful language.

No comments:

Post a Comment

Please post your comment here. ;)