Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Cube in SQL Server

Suggested Videos
Part 100 - Send datatable as parameter to stored procedure
Part 101 - Grouping sets in SQL Server
Part 102 - Rollup in SQL Server



Cube() in SQL Server produces the result set by generating all combinations of columns specified in GROUP BY CUBE(). 



Let us understand Cube() in SQL Server with examples. We will use the following Employees table for the examples in this video. 
Employees Table

Write a query to retrieve Sum of Salary grouped by all combinations of the following 2 columns as well as Grand Total.
Country,
Gender 

The output of the query should be as shown below
sql server group by with cube

Using Cube with Group By
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Cube(Country, Gender)

--OR

SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Country, Gender with Cube

The above query is equivalent to the following Grouping Sets query
SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY
    GROUPING SETS
    (
         (Country, Gender),
         (Country),
         (Gender),
         ()
    )

The above query is equivalent to the following UNION ALL query. While the data in the result set is the same, the ordering is not. Use ORDER BY to control the ordering of rows in the result set.

SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Country, Gender

UNION ALL

SELECT Country, NULL, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Country

UNION ALL

SELECT NULL, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Gender

UNION ALL

SELECT NULL, NULL, SUM(Salary) AS TotalSalary
FROM Employees

1 comment:

  1. Thanks Venkat Sir,for wonderfull effort.I know you have discussed extensivley Web services Using SOAP.But most of the organization prefer to use REST web services Please make a single tutorial how we can make Web services using Rest.I search on web so many times but did not get any solid way of making REST Web services.

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.