473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

What am I doing wrong here?

(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

Oct 25 '06 #1
3 3669
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
Oct 25 '06 #2
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
Oct 25 '06 #3
Thanks

Oct 25 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
17
by: Paul | last post by:
HI! I get an error with this code. <SCRIPT language="JavaScript"> If (ifp==""){ ifp="default.htm"} //--></SCRIPT> Basicly I want my iframe to have a default page if the user enters in...
5
by: plugimi | last post by:
hi i've got kind of a strange problem i'm struggeling with here. this page: http://www.pohflepp.de/eavesdripping4.html won't be recognized as html/xml by firefox. i've tried everything that...
4
by: Paul | last post by:
HI! I have a script that does not seem to work. can someone tell me what I am doing wrong here? <script language="JavaScript"> function firefoxautofix(){ parent.window.resizeBy(-1,-1)...
121
by: typingcat | last post by:
First of all, I'm an Asian and I need to input Japanese, Korean and so on. I've tried many PHP IDEs today, but almost non of them supported Unicode (UTF-8) file. I've found that the only Unicode...
46
by: Keith K | last post by:
Having developed with VB since 1992, I am now VERY interested in C#. I've written several applications with C# and I do enjoy the language. What C# Needs: There are a few things that I do...
13
by: Jason Huang | last post by:
Hi, Would someone explain the following coding more detail for me? What's the ( ) for? CurrentText = (TextBox)e.Item.Cells.Controls; Thanks. Jason
2
by: Aaron Ackerman | last post by:
I cannot a row to this bound DataGrid to SAVE MY LIFE! I have tried everything and I am at a loss. The using goes into add mode with the add button adds his data then updates with the update...
98
by: tjb | last post by:
I often see code like this: /// <summary> /// Removes a node. /// </summary> /// <param name="node">The node to remove.</param> public void RemoveNode(Node node) { <...> }
8
by: watkinsdev | last post by:
Hi, I have created a mesh class in visual studio 6.0 c++. I can create a device, render objects and can edit the objects by for instancnce selecting a cluster of vertices and processing the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.