write a query to caculate count ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

write a query to caculate count ?

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-22 : 15:59:06

how to write a query to caculate count ? if get data from select, add one to count, something like this:


declare @count = 0;
if(exists(select 1 from mytable where id='22')){
    @count = @count + 1;
}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-22 : 16:14:35

select @count = @count + 1
from yourtable
where id = 22

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-22 : 22:09:14

Thanks. if I want to print ID out if that record is not found, how to do it ? write another select statement ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 23:45:12

quote:
Originally posted by java148

Thanks. if I want to print ID out if that record is not found, how to do it ? write another select statement ?

hmm..what does that mean?
if record is not found, which id you've to print?
are you try to match records between two tables based on id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-23 : 00:10:30

i think you want this

DROP TABLE #tmp
CREATE TABLE #tmp (id VARCHAR(max))
INSERT INTO #tmp
(id)
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16

DECLARE @ids VARCHAR(MAX)
SET @ids='12,16,18,13'
SELECT * FROM dbo.Split(@ids,',') AS t
WHERE t.items NOT IN (SELECT id FROM #tmp )

Get split function script from below
ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-23 : 10:29:37

what I want to do is like this:


declare @count int
set @count = 0;

if(exists(select 1 from mytbl where id = '111')){
    @count = @count + 1;
}
else {
    print 'id 111 is not found in mytbl';
}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-23 : 11:34:43

[code]
declare @count int, @i int, @id int
select @count = 0, @i = @count, @id = 111

select @count = @count + 1
from yourtable
where id = @id

if @i = @count
print 'id ' + convert(varchar(10), @id) + ' is not found in mytbl'
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 01:30:54

If exists(select * from from yourtable
where id = '111')
print 'found'
else
print 'not found'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources