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.
| Author |
Topic |
|
gfaryd
Starting Member
27 Posts |
 Posted - 2011-08-15 : 14:03:24
|
| select main , submain,serial,count(case when gno is not null then gno end),count(case when mainid is not null then mainid end) ,count(case when isvalid = '1' then count(distinct mainid) end) from testtabgroup by main , submain,serialit gives me error on last statement |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gfaryd
Starting Member
27 Posts |
Posted - 2011-08-15 : 14:19:58
|
| following is the errorServer: Msg 130, Level 15, State 1, Line 2Cannot perform an aggregate function on an expression containing an aggregate or a subquery. |
 |
|
|
Aleph_0
Yak Posting Veteran
79 Posts |
Posted - 2011-08-15 : 14:24:15
|
| I think I can answer this one! You can't put a COUNT within a COUNT (I'm sure there are exceptions though). |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-08-15 : 16:31:02
|
| gfaryd, check if this is what you're looking for!SElect main , submain,serial ,count(case when gno is not null then gno end) ,count(case when mainid is not null then mainid end) ,SUM(case when isvalid = '1' then 1 else 0 end) from testtabgroup by main,submain,serialCheersMIK |
|
|
|
|
|
|
|
|
correct query