What am I doing wrong here?

[James Foreman]

(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

[Roy Harvey]

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"
<ja**********************@gmail.comwrote:

>(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

[Robert Klemme]

On 25.10.2006 12:37, James Foreman 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.

Try:

SELECT month, 'This Month' as bespokeColumnHeading, count(*)
FROM dimDate
WHERE month = '2006-10-01'
GROUP BY month;

Kind regards

robert

[James Foreman]

Thanks