Wednesday, September 3, 2014

GROUPING SETS in MS SQL

I have been doing some reporting work today. In that report I have to show some trending. In fact I have to show average salary based on the city and department. I was thinking what will be the easiest option to display in that manner, only thing is I don’t want to write huge SQL query to show this data.  Nothing to worry now, you have an option in MS SQL that is nothing by grouping set.
Today we will see how we can use GROUPING SET in SQL query and when exactly we can use this query.
First we will create a table with the name tblStatistics.

CREATE TABLE [dbo].[tblStatistics](
    [ID] [numeric](18, 0) NULL,
    [Name] [varchar](50) NULL,
    [Salary] [numeric](18, 0) NULL,
    [City] [nchar](20) NULL
) ON [PRIMARY]

GO
Now we will insert some data and query the table.
image
What I want from the above table is, I wanted to know the average salary by City.
To get the above result query we have to write here is,
SELECT avg([Salary]),[City]
  FROM [tblStatistics]
  group by 
  grouping sets (
                    (City)
                )

You can see that I am taking the avg of salary and then keeping the City inside Grouping set.
We will see the out put of the above query now,
image
You can do your own experiment as GROUPING SET can be used in many places. This is just a simple example to begin with.

No comments: