April 2, 2009 at 10:34 am
I have a select statement where I am trying to avoid duplicating a tedious section of code in the GROUP BY and ORDER BY clauses. Referencing by column number in the ORDER BY works fine, but I get an error when I try it in the GROUP BY ('Each GROUP BY expression must contain at least one column that is not an outer reference'). Is it not possible to use column number in the GROUP BY clause? Sorry for the ugly formatting, hard to control in web form...
SELECTRowGroupTitle,--Col 1
RowTitle,--Col 2
CASE @ColumnDataType--Col 3
WHEN 'Date/Time' THEN
CASE @ColumnDateUnits
WHEN 'Day' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + RIGHT('0' + CAST(DATEPART(mm, r.ColumnValue) AS varchar), 2) + '-' + RIGHT('0' + CAST(DATEPART(dd, r.ColumnValue) AS varchar), 2)
WHEN 'Month' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + RIGHT('0' + CAST(DATEPART(mm, r.ColumnValue) AS varchar), 2)
WHEN 'Quarter' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + CAST(DATEPART(qq, r.ColumnValue) AS varchar)
WHEN 'Year' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar)
END
ELSE r.ColumnValue
END AS ColTitle,
SUM(Measure)--Col4
FROMblah, blah, blah
GROUP BY RowGroupTitle, RowTitle, 3 --This gives error 'Each GROUP BY expression must contain at least one column that is not an outer reference'
ORDER BY RowGroupTitle, RowTitle, 3--This works.
When I paste the ugly CASE in place of the 3 for the GROUP BY, it works fine. I just hate to duplicate the code if I can avoid it. Any thoughts?
April 2, 2009 at 10:54 am
Group by doesn't take Column Numbers. You need to specify the columns which are doens't have any Group functions.
April 2, 2009 at 11:18 am
You can convert your query to use either derived tables or common table expressions (CTE, available in sql 2005+)
For. e.g.
SELECT MyDerivedColumn
FROM (
SELECT Col1 + Col2 AS MyDerivedColumn, Col1, Col2, Col3
FROM SomeTable
) D
GROUP BY MyDerivedColumn
ORDER BY Col1, MyDerivedColumn
;WITH DerivedCTE
AS
(
SELECT Col1 + Col2 AS MyDerivedColumn, Col1, Col2, Col3
FROM SomeTable
)
SELECT MyDerivedColumn
FROM DerivedCTE
GROUP BY MyDerivedColumn
ORDER BY Col1, MyDerivedColumn
--Ramesh
April 2, 2009 at 11:57 am
Thanks for both responses. Sound like changing it doens't really simplify it so I'll stick with what I have. Good to know the options tho, thanks much!
April 2, 2009 at 1:25 pm
Stef,
What am I missing here? Why do you say "Sound like changing it doens't really simplify it..."?
Taking the suggestion of Ramesh and wrapping the case statement in a CTE allows us to specify that case statement just once. It seems to me we end up with:
-- Less typing.
-- Less chance of erroneous inconsistency between the select and group-by clauses.
-- Easier to read code.
;with DerivedCTE as
(
SELECT RowGroupTitle, --Col 1
RowTitle, --Col 2
CASE @ColumnDataType --Col 3
WHEN 'Date/Time' THEN
CASE @ColumnDateUnits
WHEN 'Day' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + RIGHT('0' + CAST(DATEPART(mm, r.ColumnValue) AS varchar), 2) + '-' + RIGHT('0' + CAST(DATEPART(dd, r.ColumnValue) AS varchar), 2)
WHEN 'Month' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + RIGHT('0' + CAST(DATEPART(mm, r.ColumnValue) AS varchar), 2)
WHEN 'Quarter' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + CAST(DATEPART(qq, r.ColumnValue) AS varchar)
WHEN 'Year' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar)
END
ELSE r.ColumnValue
END AS ColTitle,
SUM(Measure) as SumMeasure --Col4
FROM blah, blah, blah
)
select RowGroupTitle,
RowTitle,
ColTitle,
SumMeasure
from DerivedCTE
GROUP BY RowGroupTitle, RowTitle, ColTitle
ORDER BY RowGroupTitle, RowTitle, ColTitle
April 2, 2009 at 3:46 pm
Because of the SUM() in the CTE SELECT list, don't I need the GROUP BY in the CTE? Which duplicates the icky CASE statement, right? Probably I don't understand CTE well enough, I am playing with it now....
April 2, 2009 at 3:58 pm
Ok, I got it. I just had to remove the SUM() from the CTE SELECT and add it to the 2nd SELECT. Thanks to all!
;with DerivedCTE as
(
SELECT RowGroupTitle, --Col 1
RowTitle, --Col 2
CASE @ColumnDataType --Col 3
WHEN 'Date/Time' THEN
CASE @ColumnDateUnits
WHEN 'Day' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + RIGHT('0' + CAST(DATEPART(mm, r.ColumnValue) AS varchar), 2) + '-' + RIGHT('0' + CAST(DATEPART(dd, r.ColumnValue) AS varchar), 2)
WHEN 'Month' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + RIGHT('0' + CAST(DATEPART(mm, r.ColumnValue) AS varchar), 2)
WHEN 'Quarter' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar) + '-' + CAST(DATEPART(qq, r.ColumnValue) AS varchar)
WHEN 'Year' THEN CAST(DATEPART(yyyy, r.ColumnValue) AS varchar)
END
ELSE r.ColumnValue
END AS ColTitle,
Measure --Col4
FROM blah, blah, blah
)
select RowGroupTitle,
RowTitle,
ColTitle,
SUM(Measure)
from DerivedCTE
GROUP BY RowGroupTitle, RowTitle, ColTitle
ORDER BY RowGroupTitle, RowTitle, ColTitle
[/quote]
April 3, 2009 at 10:41 pm
Thought I'd throw in an afternote. I recently read in Karen Delaney's 2005 internals book that when columns are dropped, the space isn't reclaimed until the clustered index is reorganized. If memory serves, column numbers aren't adjusted to show only the visible columns, so ordering by column number could lead to unpredictable results.
Perhaps Gail or another guru could confirm this.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply