|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
April 19th, 2004, 10:24 AM
|
|||
|
|||
|
Error Message with group by in the subquery
I have a query where I use group by in the subquery and I am getting an error message.
Query: select substring(census,1,4)as Station,IncidentNumber,InterstateNumber, (convert(decimal(12,2),IncidentNumber)/convert(decimal(12,2),InterstateNumber))*100 as Census3 from (select census,count(inci_no)as IncidentNumber,(select count(inci_no) from dbo.inc_main where substring(census,6,2) between 49 and 100 group by census) as InterstateNumber from dbo.inc_main group by census ) as mytable Error Message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How can I fix it? Please help. Thanks.
|
|
#2
April 19th, 2004, 10:10 PM
|
||||
|
||||
|
you can fix it by removing the GROUP BY highlighted in red --
Code:
select substring(census,1,4) as Station
, IncidentNumber
, InterstateNumber
, ( convert(decimal(12,2),IncidentNumber)
/convert(decimal(12,2),InterstateNumber)
)*100 as Census3
from (
select census
, count(inci_no) as IncidentNumber
, ( select count(inci_no)
from dbo.inc_main
where substring(census,6,2)
between 49 and 100
group
by census
) as InterstateNumber
from dbo.inc_main
group
by census
) as mytable
i have no idea if this will give you the results that you need, but it will resolve your syntax error
|
|
#3
April 21st, 2004, 09:19 PM
|
|||
|
|||
|
Edited...thought r937 was saying to add in the group by. But ya, take it out, any values that are in the group by must be in the select, in the part r937 pointed out, 'census' isn't.
Try that and see what you get... Last edited by Username=NULL : April 21st, 2004 at 09:32 PM.
|
|
#4
April 21st, 2004, 09:22 PM
|
|||
|
|||
|
query removed...
Last edited by Username=NULL : April 21st, 2004 at 09:40 PM.
|
|
#5
April 21st, 2004, 09:28 PM
|
||||
|
||||
|
Quote:
i guess you musta missed the part where i said to take it out, eh your inner most subquery has the same problem -- Code:
select count(inci_no)
from dbo.inc_main
where substring(census,6,2)
between 49 and 100
group by census
the problem with this is that if there is more than one census, the GROUP BY on census means that the subquery returns more than one result if it runs by itself, that's no biggie, but you cannot put it into a SELECT list then the only want you can put a subquery into a SELECT list is if it returns only one value make sense? if not, holler and i'll try to explain it again
|
|
#6
April 21st, 2004, 09:33 PM
|
|||
|
|||
|
no it does...look @ my post above, I must've been editing it as you were typing...my apologies though. I ran into that same prob today @ work, trying to run a query where a field was in the group by but not in the select...good catch.
|
|
#8
April 21st, 2004, 09:39 PM
|
|||
|
|||
|
I meant the post above that one (the one you quoted). I'm taking out that query now though.
|
|
#9
April 21st, 2004, 10:15 PM
|
||||
|
||||
|
yes, i do often say that any column used in a GROUP BY must be in the SELECT
but that's not the problem in this case in this case, you have a structure like this: SELECT a, b, (select ...) as c FROM ... in this structure, the subquery must return only one value if it has a GROUP BY in it, it can return more than one value that's what's wrong
|
|
#10
April 21st, 2004, 10:38 PM
|
|||
|
|||
|
arrgg...nevermind, I was getting off topic too much, but yes, I do understand the prob now...thx for clarifying.
Last edited by Username=NULL : April 21st, 2004 at 10:43 PM.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Error Message with group by in the subquery |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
