Grouping Sets

,

As per Question,

  1. As 1st Step, i created table in Sample Database (I used NORTHWIND) for the same.

use northwind

go

create table #T (Customer varchar(100),Year int,Sales money);

go

insert into #T (Customer, Year, Sales)values

('Jack', 2010, 10)

,('Jack', 2010, 15)

,('Bob', 2009, 5)

,('Dick',2011, 20);

go

       2. Then 2nd step, i queried for the records of #T (table) whether the 4 records have been successfully inserted.

use northwind

go

SELECT * FROM #T ORDER BY YEAR ASC

go

     3. After checking the above query, i further executed the following:-

use northwind

go

SELECT NULL as customer, year, SUM(sales)FROM #T GROUP BY year

UNION ALL

SELECT customer, NULL as year, SUM(sales)FROM #T GROUP BY customer;

go

    4. Further to that, i cross-checked from the following query with the above UNION ALL grouped query syntax :-

use northwind

go

SELECT customer, year, SUM(sales)FROM #T

GROUP BY GROUPING SETS ((customer), (year));

go

  • FINAL CONCLUSION: - RESULTSET's are matching exactly while running both queries.
Question Of The Day
===================

create table #T (Customer varchar(100),Year int,Sales money);
insert into #T (Customer, Year, Sales)values
('Jack', 2010, 10)
,('Jack', 2010, 15)
,('Bob', 2009, 5)
,('Dick',2011, 20); 

Is this select

SELECT NULL as customer, year, SUM(sales)FROM #T GROUP BY year
UNION ALL
SELECT customer, NULL as year, SUM(sales)FROM #T GROUP BY customer;equivalent or the same as this select?

SELECT customer, year, SUM(sales)FROM #T
GROUP BY GROUPING SETS ((customer), (year));

Rate

1.6 (5)

Share

Share

Rate

1.6 (5)