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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Count

Author

Topic

pssumesh2003
Starting Member

35 Posts

Posted - 2010-10-27 : 04:42:46

hi

I have a table having 7 different column with numeric field.

I want to find count of each column have'nt value 0 (Zero)
plz help me?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 05:01:19

Hint:

Case when Col1<>0 then 1 else 0 end
+
Case when Col2<>0 then 1 else 0 end

pssumesh2003
Starting Member

35 Posts

Posted - 2010-10-27 : 07:03:17

i think u did'nt follow my qn.
my table structure

EmpId S su M T W Th F

where S Su M T W Th F are numeric filed ,

My qn is i want find Count S su M T W Th F having value <>0 using function 'Count'

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-27 : 07:18:58

Select count(column1) AS col1
From MyTable
Where Column1='0'

Select count(column2) AS col2
From MyTable
Where Column2='0'

Select count(column2) As col3
From MyTable
Where Column3='0'

GO

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-27 : 07:20:16

quote:
Originally posted by sz1

Select count(column1) AS col1
From MyTable
Where Column1='0'

Select count(column2) AS col2
From MyTable
Where Column2='0'

Select count(column3) As col3
From MyTable
Where Column3='0'

GO

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 07:21:37

Are you looking for one of the below two statements ?

--Separate sum value
Select
EmpID,
Count(Case When S <> 0 then 1 else null end) as SCount,
Count(Case When Su <> 0 then 1 else null end) as SuCount,
Count(Case When M <> 0 then 1 else null end) as MCount
from YourTable

--Single sum value

Select
EmpID,
Case When S <> 0 then 1 else 0 end +
Case When Su <> 0 then 1 else 0 end +
Case When M <> 0 then 1 else 0 end As SingleSum
From Your Table

If still my understanding is wrong then please provide sample data and expected output.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pssumesh2003
Starting Member

35 Posts

Posted - 2010-10-27 : 07:40:23

sorry pk_bohra
Its my mistake.
It is right

Thank u very much

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 07:50:37

quote:
Originally posted by pssumesh2003

sorry pk_bohra
Its my mistake.
It is right

Thank u very much

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources