How to get Same Row value Count from table

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)

How to get Same Row value Count from table

Author

Topic

ssmani84
Starting Member

20 Posts

Posted - 2010-04-09 : 03:01:11

Hi friends,

i want to get Same Row value Count From Table In sqlserver2005

My table Structure Is below

ID Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8
5401 NULL P P P P H H P

here i want to get Total No of P values and Total No of H values ,Is this possible?

ssmaniyadav

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-09 : 03:21:56

Hi,

One of the way to get the output in reqd format:

Declare @Test table
(
Id int,
Day1 char,
Day2 char,
Day3 char,
Day4 char
)

Insert into @Test
Values (1,'P','P','H','P')

Select id,
Sum(
(case Day1
When 'P' then 1
else 0 end )
+
(case Day2
When 'P' then 1
else 0 end
)
+
(case Day3
When 'P' then 1
else 0 end
)
+
(case Day4
When 'P' then 1
else 0 end
)
)
As CountOfP,

Sum(
(case Day1
When 'H' then 1
else 0 end )
+
(case Day2
When 'H' then 1
else 0 end
)
+
(case Day3
When 'H' then 1
else 0 end
)+
(case Day4
When 'H' then 1
else 0 end
)
)

As CountOfH
From @Test
Group by id

I am not sure whether this is the best way of getting the reqd output.

Regards,
Bohra

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 03:26:13

One method is


select id,
case when day1='H' then 1 else 0 end+
case when day2='H' then 1 else 0 end+
case when day3='H' then 1 else 0 end+
case when day4='H' then 1 else 0 end+
case when day5='H' then 1 else 0 end+
case when day6='H' then 1 else 0 end+
case when day7='H' then 1 else 0 end+
case when day8='H' then 1 else 0 end as H,
case when day1='P' then 1 else 0 end+
case when day2='P' then 1 else 0 end+
case when day3='P' then 1 else 0 end+
case when day4='P' then 1 else 0 end+
case when day5='P' then 1 else 0 end+
case when day6='P' then 1 else 0 end+
case when day7='P' then 1 else 0 end+
case when day8='P' then 1 else 0 end as P
from your_table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 03:29:29

Madhivanan

Failing to plan is Planning to fail

ssmani84
Starting Member

20 Posts

Posted - 2010-04-09 : 05:23:12

Thanks madhivanan......thank u so much..Your query is working fine thanks a lot

ssmaniyadav

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 07:11:57

quote:
Originally posted by ssmani84

Thanks madhivanan......thank u so much..Your query is working fine thanks a lot

ssmaniyadav

You are welcome

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-04-10 : 02:28:03

ONE MORE approach is u can convert the query set as xml using xml auto, elements and getting the count of p's and h's

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources