(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 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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)...
|
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...
|
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...
|
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
|
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...
|
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) {
<...>
}
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |