|
|
Posted by James Foreman on 10/25/06 10:37
(SQL Server 2000)
I want to aggregate some data from a table, and because I'm going to
feed the result set into a union, say, I want to put another column on
there to carry some information that I know will always be the same in
this particular result set.
Simple example: I have a table DimDate with two columns, date and
month. I want to bring back a count of how many days there are in the
current month and the previous month, and I want to use a union to do
it. [Yes, I *know* there's probably simpler ways to do this with
datediff functions, etc, but I don't want to give you the real example
which aggregates lots of columns from a bunch of fact tables, etc etc
yada yada yada...]
SELECT month, 'This Month' as bespokeColumnHeading, count(*)
FROM dimDate
WHERE month = '2006-10-01'
GROUP BY month, 'This Month';
gives me:
Server: Msg 164, Level 15, State 1, Line 3
GROUP BY expressions must refer to column names that appear in the
select list.
whereas
SELECT month, 'This Month' as bespokeColumnHeading, count(*)
FROM dimDate
WHERE month = '2006-10-01'
GROUP BY month, bespokeColumnHeading;
gives me:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'bespokeColumnHeading'.
So, er, what do I do? I could do a nested query:
SELECT month, bespokeColumnHeading, count(*)
FROM
(SELECT month, 'This Month' bespokeColumnHeading, date
FROM dimDate WHERE month = '2006-10-01') as a
GROUP BY month, bespokeColumnHeading;
but that's a really ugly workaround, and I'd like to know if there was
a proper way to do this.
Thanks
James
Navigation:
[Reply to this message]
|