Error Message with group by in the subquery

[ichervin]

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.

[MAK]

select count(inci_no) from dbo.inc_main where substring(census,6,2) > 49 and
substring(census,6,2) < 100) group by census

[ichervin]

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.

[MAK]

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.

[ichervin]

Let's say the value for the census is 2345.99. Can you show me how to modify the query?

[MAK]

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

[ichervin]

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.

[MAK]

please post sample data and expected results, so that it will be easy for us to produce a query.

[ichervin]

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

[ichervin]

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

[ichervin]

D should be C/B * 100

[MAK]

--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

[ichervin]

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.

[MAK]

please post the output result that you are looking for based on the sample data given.

[ichervin]

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