Can you nest parentheses in GROUPING SETS?

[TamarGranor]

I'll start by saying that I'm doing this just to learn how these things work, so I can use them when I need them and so I can write about them. I've been playing around with ROLLUP, CUBE and GROUPING SETS. At the moment, I'm trying to write the GROUPING SETS equivalent of a query that I've already got working with CUBE.

Using AdventureWorks 2008, I have a query that does aggregation of sales data. The CUBE version looks like this:

SELECT Person.CountryRegion.Name,
       Person.Address.City,
       YEAR(OrderDate) AS nYear, 
       MONTH(OrderDate) AS nMonth, 
       SUM(SubTotal) AS TotalSales,
       AVG(SubTotal) AS AvgSale,
       COUNT(SubTotal) AS NumSales      
	FROM Sales.Customer
	  JOIN Person.Person
	    ON Customer.PersonID = Person.BusinessEntityID
	  JOIN Person.BusinessEntityAddress
	    ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
	  JOIN Person.Address
	    ON BusinessEntityAddress.AddressID = Address.AddressID 
	  JOIN Person.StateProvince
	    ON Address.StateProvinceID = StateProvince.StateProvinceID
	  JOIN Person.CountryRegion
	    ON StateProvince.CountryRegionCode = CountryRegion.CountryRegionCode
	  JOIN Sales.SalesOrderHeader
	    ON Customer.CustomerID = SalesOrderHeader.CustomerID
	  JOIN Sales.SalesOrderDetail
	    ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID	
	GROUP BY CUBE((CountryRegion.Name, Address.City), YEAR(OrderDate), MONTH(OrderDate))
	ORDER BY Name, City, nYear, nMonth

Note that CountryRegion.Name and Address.City are grouped together for the cube, because some city names appear in multiple countries.

Now I'm trying to do the same thing with Grouping Sets. Everything up to the GROUP BY is identical to the query above. Here's what I'm trying in the GROUP BY:

	GROUP BY GROUPING SETS(
	  ((CountryRegion.Name, Address.City), YEAR(OrderDate), MONTH(OrderDate)),
	  ((CountryRegion.Name, Address.City), YEAR(OrderDate)),
	  ((CountryRegion.Name, Address.City), MONTH(OrderDate)),
	  ((CountryRegion.Name, Address.City)),
	  (YEAR(OrderDate), MONTH(OrderDate)),
	  (YEAR(OrderDate)),
	  (MONTH(OrderDate)),
	  ())

SQL Server 2008 is giving me errors all around the (CountryRegion.Name, Address.City) combinations. The comma in the first two occurrences gets "Incorrect syntax near ','", while the right paren at the end of the second occurrence shows "Incorrect syntax near ')'" and a number of the fields after that have "Incorrect syntax near <fieldname>. Expecting '(' or SELECT."

I'm reasonably sure all the parens are correctly matched and the syntax is otherwise correct. So is this simply not permitted? If so, is there another way to get this effect with grouping sets.

Thanks,
Tamar

 

[imex]

Try:

GROUP BY GROUPING SETS(
	  (CountryRegion.Name, Address.City, YEAR(OrderDate), MONTH(OrderDate)),
	  (CountryRegion.Name, Address.City, YEAR(OrderDate)),
	  (CountryRegion.Name, Address.City, MONTH(OrderDate)),
	  (CountryRegion.Name, Address.City),
	  (YEAR(OrderDate), MONTH(OrderDate)),
	  (YEAR(OrderDate)),
	  (MONTH(OrderDate)),
	  ())

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[TamarGranor]

Duh, of course. I was so wrapped up in needing the parens to ensure I didn't separate city and country that I failed to see that simply omitting the cases where I use only one or the other would do the trick.

Thanks,
Tamar