SQL Server Performance Forum – Threads Archive

Count

Declare @companyids varchar(1000)
select @companyids = ‘17068,72947,17069,56650’
Declare @Str1 varchar(1000)
Select @str1 = ‘select companyid, count(*)
from companycontact cc WITH (NOLOCK)
where cc.companyid in (‘ + @companyids + ‘) and cc.activeYN = ”Y”
group by companyid ‘
Exec (@str1) Above query return the output
17068 1
17069 5
56650 1 This is not showing companyid 72947 because count is zero(0). I want to show that row in result. Is there any trick to do this?
Surendra Kalekar

Something like this Declare @t table(i int, value int)
insert into @t values(1,23)
insert into @t values(1,65)
insert into @t values(2,3)
insert into @t values(3,634) Declare @t1 table(i int, value int)
insert into @t1 values(1,0)
insert into @t1 values(2,0)
insert into @t1 values(3,0)
insert into @t1 values(4,0)
insert into @t1 values(5,0) select i, max(val) from (
select i,sum(value) as val from @t group by i
union all
select * from @t1) T
group by i Madhivanan Failing to plan is Planning to fail

Hi Surendra, TRy getting flag this way:::::: declare @table1 table(comid int) insert into @table1 values(1706<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> insert into @table1 values(72947) insert into @table1 values(17069) insert into @table1 values(56650) select comid,count(companyid) from companycontact cc right outer join @table1 t on cc.companyid=t.comid group by t.comid

<pre id="code"> Declare @companyids varchar(1000) select @companyids = ‘17068,72947,17069,56650’ Declare @Str1 varchar(1000) declare @table1 table(comid int) insert into @table1 values(1706<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> insert into @table1 values(72947) insert into @table1 values(17069) insert into @table1 values(56650) select companyid, count(*) from companycontact cc WITH (NOLOCK) where cc.companyid in (17068,72947,17069,56650) group by companyid union select comid,0 from @table1 cc where comid not in (select companyid from companycontact) </pre id="code"> also do the job —————————————- Cast your vote http://www.geocities.com/dineshasanka/sqlserver05.html</a> http://spaces.msn.com/members/dineshasanka</a>

Thanks for your reply.
You both are suggested me to use table. Is there any way without inseting those companyid into table becuase in this case companyid’s can be more than 1000. In our orignial sp we are using
@companyids as varchar(8000) Surendra Kalekar

http://www.sommarskog.se/arrays-in-sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

what you can do is, write a simple scritpt to insert into the table variable from the varchar variable —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Hi Surendra,
Even i think best way is to split the ids from string into table variable and then apply any of the above method.

Create FUNCTION array_convert (@list varchar(1000))
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000) SET @textpos = 1
SET @leftover = ”
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 – datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen SET @pos = charindex(‘,’, @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos – 1)
INSERT @tbl (number) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(‘,’, @tmpstr)
END SET @leftover = @tmpstr
END IF ltrim(rtrim(@leftover)) <> ”
INSERT @tbl (number) VALUES(convert(int, @leftover)) RETURN
END if you run declare @regno as varchar(500)
set @regno = ‘1,2,3’ select number from array_convert(@regno) you don’t have to insert. instead you can use the original varible of yours —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Thenyour quesry would look like this Declare @companyids varchar(1000)
select @companyids = ‘17068,72947,17069,56650’
select companyid, count(*)
from companycontact cc WITH (NOLOCK)
where cc.companyid in (17068,72947,17069,56650)
group by companyid
union
select number,0
from array_convert ( @companyids)
where number not in (select companyid from companycontact)
—————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Why all the difficult stuff? You just need to make sure WHAT you’re counting, not how you’re counting.
select c.companyid, count(cc.companyid)
from company c
left join companycontact cc WITH (NOLOCK)
on c.companyid = cc.companyid
where c.companyid in (17068,72947,17069,56650)
group by c.companyid

Thanks everybody.
This solution will help me… need to change my query with Adriaan’s solution.

quote:Originally posted by Adriaan Why all the difficult stuff? You just need to make sure WHAT you’re counting, not how you’re counting.
select c.companyid, count(cc.companyid)
from company c
left join companycontact cc WITH (NOLOCK)
on c.companyid = cc.companyid
where c.companyid in (17068,72947,17069,56650)
group by c.companyid

Surendra Kalekar

Anyway yo need to have two tables [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

You are right, but insted of creating & inserting data I think this is better. Don’t you think so? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan Anyway yo need to have two tables [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> <h6>Surendra Kalekar</h6>

Yes. But you didnot tell us there is master Table that you are using until Adriaan understand it [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Yes, you can say it was my mistek [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] But I was also in the same impression that there is no need of second table and there may be some trik to do it… After reading the posting of Adriaan I started thinking to change the SQL in such way. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan Yes. But you didnot tell us there is master Table that you are using until Adriaan understand it [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> <h6>Surendra Kalekar</h6>

]]>