|
|
Posted by Roy Harvey on 10/25/06 11:20
There is a workaround, and it is simple. Don't reference that column
in the GROUP BY.
Consider this example.
select xtype, 'banana', count(*)
from syscolumns
group by xtype
xtype
----- ------ -----------
34 banana 3
35 banana 6
36 banana 3
48 banana 26
52 banana 105
56 banana 152
58 banana 5
60 banana 3
61 banana 13
98 banana 1
104 banana 8
106 banana 12
108 banana 5
127 banana 4
165 banana 16
167 banana 77
173 banana 4
175 banana 23
231 banana 50
239 banana 4
(20 row(s) affected)
Roy Harvey
Beacon Falls, CT
On 25 Oct 2006 03:37:52 -0700, "James Foreman"
<jamesconradstjohnforeman@gmail.com> wrote:
>(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]
|