-
Error Message with group by in the subquery
Hi,
Using group by in the subquery returns an error message.
Subquery:
select count(inci_no)
from dbo.inc_main
where substring(census,6,2) > 49 and
substring(census,6,2) < 100) as Census2
group by census
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 to fix it? Thanks.
-
select count(inci_no) from dbo.inc_main where substring(census,6,2) > 49 and
substring(census,6,2) < 100) group by census
-
Error Message with group by in the subquery Post 2335
I have tried that and I am getting another error message.
Here is my complete 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.
I need a fix. Thanks.
-
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
is returning more than one value. remove group by and add hardcode a condition for a particular census.
-
Error Message with group by in the subquery Post 2337
Let's say the value for the census is 2345.99. Can you show me how to modify the 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 ) as InterstateNumber
from dbo.inc_main group by census
) as mytable
-
Error Message with group by in the subquery
I already have this query. This is not exactly what I am looking for. The second count gives the total of all the census, but I need the count per census. Thanks.
-
please post sample data and expected results, so that it will be easy for us to produce a query.
-
Error Message with group by in the subquery
Here is the info you've asked for:
Sample data
------------
col1 col2
------- ---------
0340.01 05-4589888
0340.01 03-4594949
0340.01 04-3949494
0348.89 04-2393939
2309.30 04-5677777
2309.30 04-3820292
2309.30 03-9584847
2387.23 04-2387999
2367.30 04-2334444
2345.20 04-6567383
2345.20 04-3930390
2312.25 05-2829898
2312.25 04-2333333
Expected results
-----------------
A B C D
first four digits Total # of col2 per # of col2 per % of col2
of col1 col1 col1 where per col1
last 2 digits of
col1 between 20 and 30
-
Error Message with group by in the subquery
Disregard the previous email.
Sample data
------------
col1 col2
------- ---------
0340.01 05-4589888
0340.01 03-4594949
0340.01 04-3949494
0348.89 04-2393939
2309.30 04-5677777
2309.30 04-3820292
2309.30 03-9584847
2387.23 04-2387999
2367.30 04-2334444
2345.20 04-6567383
2345.20 04-3930390
2312.25 05-2829898
2312.25 04-2333333
Expected results
-----------------
A
first four digits of col1
B
Total # of col2 per col1
C
# of col2 per col1 where last 2 digits of col1 between 20 and 30
D
% of col2 per col1
-
Error Message with group by in the subquery
-
--I believe this is the answer you are looking for....
create table mytablex(col1 varchar(100), col2 varchar(100))
insert into mytablex select '0340.01','05-4589888'
insert into mytablex select '0340.01','03-4594949'
insert into mytablex select '0340.01','04-3949494'
insert into mytablex select '0348.89','04-2393939'
insert into mytablex select '2309.30','04-5677777'
insert into mytablex select '2309.30','04-3820292'
insert into mytablex select '2309.30','03-9584847'
insert into mytablex select '2387.23','04-2387999'
insert into mytablex select '2367.30','04-2334444'
insert into mytablex select '2345.20','04-6567383'
insert into mytablex select '2345.20','04-3930390'
insert into mytablex select '2312.25','05-2829898'
insert into mytablex select '2312.25','04-2333333'
Query
------
Select A,B,C,D=(convert(decimal(25,5),C)/convert(decimal(25,5),B)) * 100 from (
select left(col1,4) as A,count(col2)as B,(select count(col2)
from mytablex where substring(col1,6,2) between 20 and 30 ) as C
from mytablex group by left(col1,4) ) as y
Last edited by MAK; 04-19-2004 at 01:57 PM.
-
Error Message with group by in the subquery
We are getting closer, except for the 2nd count I need to know how many C's are in B's and then calculate the percentage.
-
please post the output result that you are looking for based on the sample data given.
-
Error Message with group by in the subquery
Here are the results I am looking for:
A
first four digits of col1
B
Total # of col2 per col1
C
total # of col2 where last 2 digits of col1 between 20 and 30 for each group in B
D
C/B*100
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|