April 24, 2016 at 9:21 pm
I'm trying to only display certain columns and order by different columns from an aggregate group by query using a grouping set. To do this I'm using a sub-query.
I have two questions:
1. Is there a better way to group by and order by diff columns?
2. Are there performance problems with using sub-query this way?
Query:
SELECT Region, State, Sales FROM (
SELECT RegionID, Region, StateID, State, SUM(Sales) AS Sales
FROM Sales
GROUP BY
GROUPING SETS(
(RegionId, Region, StateID, State),
(StateId, State),
()
)
) As T1
ORDER BY RegionID, StateID
April 25, 2016 at 2:49 pm
ptownbro (4/24/2016)
I'm trying to only display certain columns and order by different columns from an aggregate group by query using a grouping set. To do this I'm using a sub-query.I have two questions:
1. Is there a better way to group by and order by diff columns?
2. Are there performance problems with using sub-query this way?
Query:
SELECT Region, State, Sales FROM (SELECT RegionID, Region, StateID, State, SUM(Sales) AS Sales
FROM Sales
GROUP BY
GROUPING SETS(
(RegionId, Region, StateID, State),
(StateId, State),
()
)
) As T1
ORDER BY RegionID, StateID
I think you do not need the subquery. Didn't test it, but I expect that this should work too:
SELECT RegionID, Region, StateID, State, SUM(Sales) AS Sales
FROM Sales
GROUP BY
GROUPING SETS(
(RegionId, Region, StateID, State),
(StateId, State),
()
)
)
ORDER BY RegionID, StateID
i don't expect any measureable performance difference between the two but you'll have to test to be sure.
April 25, 2016 at 4:17 pm
Thanks for the response. However, you may have misunderstood my question.
I already know that in my order by I don't need to include all the columns listed in my select statement. What I want to do is display different columns in my select statement than what's in my order by statement
April 25, 2016 at 5:31 pm
Here's a better example of what I mean. I'm sorting based on different fields than what is being displayed.
SELECT Region, State, SUM(Sales) AS Sales
FROM Sales
GROUP BY
GROUPING SETS(
(Region, State),
(State),
()
)
ORDER BY RegionID, StateID
The above won't work. So, what I did instead was include everything in the sub-query and then, in the main query, selected what I wanted. Shown again here:
SELECT Region, State, Sales FROM (
SELECT RegionID, Region, StateID, State, SUM(Sales) AS Sales
FROM Sales
GROUP BY
GROUPING SETS(
(RegionID, Region, StateID, State),
(StateID, State),
()
)
) T1
ORDER BY RegionID, StateID
April 26, 2016 at 9:46 am
The GROUP BY clause creates a grouping set and you can only select or order by properties of the grouping set. A grouping set has two kinds of properties defining properties and aggregate properties. Defining properties are true of each record within the set and aggregate properties are an amalgam of all the records in the set. You're trying to order by a property that is neither a defining property nor an aggregate. You either need to include that field in the group definition or use an aggregate, such as MIN() or MAX().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply