how to write this subquery? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to write this subquery?

Author

Topic

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-01-15 : 00:50:37

Hi all ,

i have a table companytable,this table columns are companyid(pk,identitycolumn),companyname(varchar(50),visit(int).
values are :
companyid:1,companyname=a,visit=5
companyid:2,companyname=b,visit=7
companyid:3,companyname=c,visit=1
companyid:4,companyname=d,visit=9

for ex:one person visited company 'd 'in 9 times so i want to display that company

what i want to do is i want to display visit wise companynames:
how to write this stored procedure with out passing any input parameter.

i wrote stored procedure like this:

ALTER procedure GetCompanyByCount
as
begin
declare @Temp_companycount int
set @Temp_companycount=(select visit from companytable where companyid=(select companyid from companytable))
select companyid,companyname from companytable
where visit=@Temp_companycount
end

but i am getting this error :

subquery is returning more than one value at this line:
set @Temp_companycount=(select visitfrom companytable where companyid=(select companyid from companytable))
Thanks & Regards
RamaDevi

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-01-15 : 01:23:23

Declare @temp_companycount int
Select companyid, companyname , Count(companyId)
from Companytable
group by companyid, companyname
having count(companyId) = @temp_companycount

Is this what you want?

The subquery return more than one value as there are multiple companyid.

check out my blog at http://www.aquariumlore.blogspot.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-15 : 02:55:28

Your procedure is quite confusing, but what is causing the actual error is what is marked in red:

set @Temp_companycount=(select count from companytable 
  where companyid=(select companyid from companytable))

When you use an equal to operation you need to return only one single row in your sub-select, this one will return all companyid's.

However, your query still doesn't make much sense. Try to run the following query by itself and see if the output is like you want:

select companyid,companyname, sum(count) AS TotalCount
from companytable
group by companyid,companyname
order by companyid,companyname

If this is not what you want, then please post what you want the output to be and we'll help you.

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 03:00:00

What is defining your search criteria?

1) Company D
2) 9 visits

E 12°55'05.63"
N 56°04'39.26"

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-01-15 : 04:32:26

Thanks to all ,
i got result using this below query:

select companyid,companyname
from companytable
group by companyid,companyname, count_hit
order by count_hit desc

Regards
RamaDevi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 04:34:58

Insane.

CompanyID is idenity column, and thus always will be unique anyway.

select companyid,companyname
from companytable
order by count_hit desc

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-15 : 04:43:00

Great that you got it to work though, even though you overcomplicated the query *quite* a lot. If you also post your desired result next time it will be a lot easier for us to give you better help.

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources