Group question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Group question

Author

Topic

sonu
Posting Yak Master

110 Posts

Posted - 2005-02-08 : 13:26:08

Hello,

i have a quert like this:

select 
   somefields
from 
  sometable
where
  someconditions = someotherconditions
Having
  y = 'bla'
group by
  x

Now the problem is that if I run the query then it tells me that "Not a group by expression"

I do not want to group by "y". How can I get rid of that?
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:28:58

will this do?
group by comes before having, because having works on already formed groups of data.

select
somefields
from
sometable
where
someconditions = someotherconditions
group by
x
Having
y = 'bla'

Go with the flow & have fun! Else fight the flow

sonu
Posting Yak Master

110 Posts

Posted - 2005-02-08 : 13:31:07

Nope, same error

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:33:45

how bout you give us the acctuall query?

Go with the flow & have fun! Else fight the flow

sonu
Posting Yak Master

110 Posts

Posted - 2005-02-08 : 13:35:14

I cant show you the original query. Sorry :(

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:45:06

well then i guess we need jeff and his crystall ball...
another option is to inner join the original query to the subquery in which you somehow use "y" to your liking...

That's about all my ESP can sense...

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-08 : 13:56:43

Don't know why sonu can't show the query, but wouldn't

select
somefields
from
sometable
where
someconditions = someotherconditions
group by
x, y
Having
y = 'bla'

do the trick?

Semper fi,

Xerxes, USMC (Ret)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-08 : 13:58:17

it would but he said that he doesn't want to group on y...

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 14:03:15

Why wouldn't it be:


select 
	somefields
from 
	sometable
where
	someconditions = someotherconditions
	
	and y = 'bla'
	
group by
	x

Having is for adding conditions to aggregated values. For example:
count(*) > 1, sum(value)>1000, or the like...

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-08 : 14:04:27

First the HAVING Need to a scalar function, second if you want to include additional Columns, they need to be part of some scalar function as well

SELECT Col1, MAX(Col2), SUM(Col3)
FROM myTable99
GROUP BY Col1
HAVING COUNT(*) > 1

Or something like that.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources