When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, April 30, 2003

Summarizing Data with CUBE Queries, Part 2

By Scott Mitchell


  • Read Part 1

  • In Part 1 we examined how to use the GROUP BY clause to apply an aggregate function to partitions of a table, as opposed to the entire table. However, as we saw, oftentimes we want to add summary data. For example, if we are using GROUP BY to sum across various partitions of the table, we might want to include a total sum for each partition dimension, as well as a grand total sum. We'll see how to accomplish this using a single SQL statement in this part.

    - continued -

    Using the CUBE Statement in a SQL Query
    In order to create a data cube all we need to do is alter our SQL statement slightly, adding WITH CUBE to the end of the query. That is, if we change our SQL query to:

    SELECT Team, [Position], SUM(Points)
    FROM PlayerStats
    GROUP BY Team, [Position]
    WITH CUBE

    We'll get the results back in a data cube. (Note that CUBE was added to SQL Server 6.5; earlier versions of SQL Server do not include this functionality.) Specifically, the results returned for our particular data set will be:

    TeamPosition 
    KingsPoint Guard875
    KingsPower Forward1542
    KingsNULL2,417
    LakersCenter1,841
    LakersPower Forward522
    LakersShooting Guard2,461
    LakersNULL4,824
    RocketsCenter1,104
    RocketsNULL1,104
    NULLNULL8,345
    NULLCenter2,945
    NULLPoint Guard875
    NULLPower Foward2,064
    NULLShooting Guard2,461

    As you can see, there are a lot of NULLs in the results. Each NULL represents a "sum" column or row. For example, the last record in the results shows the sum of all points for Shooting Guards. The result with two NULLs shows the total points across all teams and positions. This query can be prettied up using the ISNULL() function to replace the NULL values with text like "All":

    SELECT ISNULL(Team, 'All'), ISNULL([Position], 'All'), SUM(Points)
    FROM PlayerStats
    GROUP BY Team, [Position]
    WITH CUBE

    Which will replace all the NULLs in the output with the text "All".

    Applications of Data Cubes
    Data cubes are used primarily in data warehousing, which is a field of database research that examines storing vast amounts of data from numerous data stores for the purpose of report information. In data warehousing, there is often data that contains many "dimensions".

    For example, a retailer like Wal-Mart might have sales data that contains the following information for each sale made:

    • The item sold
    • The store location the item was sold from
    • The date the sale was made
    • What method of payment was used (credit card/cash/check/etc.)

    Using this information, a four-dimensional data cube could be constructed. With this data cube, executives could examine data from a very fine-level of detail to a very broad-level. For example, an executive might want to see the total number of XBox sales in Wal-Mart store #1233 sold on May 4th, 2003 and paid for with cash. A broader query might be to see how many XBoxes were sold in all of the locations on May 4th, 2003, or how many XBoxes has store #4421 sold in total? Or how many items were sold in store #2111 and paid for by check? Even broader queries could be along the lines of how many total products has store #5331 sold? Or, how many total XBoxes have sold at all Wal-Mart stores? Or, the broadest possible query: How many total items have been sold across all stores, all days, and all payment methods?

    For more information on data cubes and the WITH CUBE syntax, check out the following articles:

    Conclusion
    In this article we examined the CUBE SQL statement and how to use it to return data cubes from a single SQL query. Data cubes, as we discussed, enhance the results from simple GROUP BY statements by providing summary data across all dimensions of the partitions of the data.

    Happy Programming!

  • By Scott Mitchell


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article