Author |
Topic |
jej1216
Starting Member
27 Posts |
Posted - 2007-09-26 : 11:19:19
|
I need to do a select on a table to return those BUSINESS_UNIT/ASSET_ID's that have three rows or less of data. I'm having trouble with the statement. I have tried:select count(*), BUSINESS_UNIT, ASSET_IDfrom FS.dbo.PS_BOOKhaving count(*)< 4group by BUSINESS_UNIT, ASSET_ID order by BUSINESS_UNIT, ASSET_ID, count (*) But get the error "Incorrect syntax near the keyword 'group'."I know the "count" cannot be in a where clause, but I'm stuck for what else to try.It's probably easier than I'm making it.TIA,jej1216 |
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-26 : 11:25:48
|
"having" goes after the "group by" and before the "order by". |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-27 : 02:50:00
|
Only WHERE should come before group by and not HAVING MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-27 : 03:07:20
|
I am happy things worked out for you, "joe".But waht goes into your mind thinking you get a faster answer posting here than open Books Online and read about SELECT FROM syntax? E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-27 : 03:13:23
|
quote: Originally posted by Peso I am happy things worked out for you, "joe".But waht goes into your mind thinking you get a faster answer posting here than open Books Online and read about SELECT FROM syntax? E 12°55'05.25"N 56°04'39.16"
He might think he could get answer quickly here MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-27 : 03:20:54
|
Yes, maybe.But he also posted the same question on at least one other site... E 12°55'05.25"N 56°04'39.16" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-27 : 03:53:39
|
quote: Originally posted by Peso Yes, maybe.But he also posted the same question on at least one other site... E 12°55'05.25"N 56°04'39.16"
Then OP follows FIFO MadhivananFailing to plan is Planning to fail |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-09-27 : 04:40:37
|
quote: Originally posted by Peso I am happy things worked out for you, "joe".But waht goes into your mind thinking you get a faster answer posting here than open Books Online and read about SELECT FROM syntax?
if you are new to SQL sometimes it is hard to figure out what BOL is trying to tell you. It can be confusing because of all the arguments and such that are available to you in a SELECT statement. the OP has asked a good question imho. he has posted what he wants to do, as well as his code that is not working. He has made a simple mistake in syntax and several people have helped. this is exactly what sqlteam is for.Remember we were all new to SQL at some point.. -ec |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-27 : 04:48:33
|
Yes I know. He got excellent help.The point I am trying to make, is this:Is the time spent posting this question on several forums more worth than open Books Online directly? E 12°55'05.25"N 56°04'39.16" |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-09-27 : 04:55:41
|
quote: Originally posted by Peso Yes I know. He got excellent help.The point I am trying to make, is this:Is the time spent posting this question on several forums more worth than open Books Online directly?
I understand what you posted peter! My point is maybe the OP read BOL but didn't understand the SELECT statement syntax shown in BOL. We are not all SQL geniuses :)-ec |
|
|
jej1216
Starting Member
27 Posts |
Posted - 2007-09-27 : 10:39:34
|
Thanks for your help.I did search the SQL Server Transact help files, and did not find the answer. I always do this before I post. As far as posting to another forum at the same time, I apologize but I was under the gun as I'm sure everyone can relate to.My company is small, has no DBA, and many times we have to scramble for answers.I greatly appreciate the help I get from forums such as this.jej1216 |
|
|
|