How to count data of a single row in sql server

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hello
I have a table and the data is like:

Mon	year	Emp_Id	D1	D2	D3	D4	D5	D6	D7	D8
Aug	2012	E001	NULL	NULL	A	P	P	P	P	NULL

In the above table It is D1,D2.....up to D30

So I want to know no. of P from D1 to D30 of a single row. Is it possible in a single query.

Please, help me.

[EDIT]Tags added - LOSMAC[/EDIT]

Posted 26-Aug-12 23:13pm

Jimut123456789

Updated 26-Aug-12 23:28pm

Maciej Los

v2

Add a Solution

4 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2012-08-26T23:19:00

Solution 1

Do the following :

SQL

select Emp_Id,d1+d2+d3+d4+d5+d6+d7+d8+d9+d10+d11++d12+d13+d14+d15+d16+d17+d18+d19+d20+d21+d22+d23+d24+d25+d26+d27+d28+d29+d30 as [total days] from [tablename]

Edit :

It is much easier and a better design to have the following table:

EMP_ID, MON, YEAR, DAY

Where you enter data based on the presence of a person hence you can do :

SQL

select EMP_ID, [MON], [YEAR], Count(DAY) from [tablename] group by Emp_ID,[MON],[YEAR]

Posted 26-Aug-12 23:19pm

Mehdi Gholam

Updated 26-Aug-12 23:53pm

v2

Comments

Santhosh Kumar Jayaraman 27-Aug-12 5:29am

I am not sure whether the above query will work. Few columns can have null value, 'A','P' and he wants to know how many columns has P. He has to use Pivot i believe

Mehdi Gholam 27-Aug-12 5:31am

wrap the values in ISNULL for example : ISNULL(d1,0) etc.

Maciej Los 27-Aug-12 5:33am

Mehdi, i think your query will not satisfy OP. Why? D1, D2 and so on fields can store different values, as A, NULL, P. OP want to count P for every single row. So, in this case P is equal 1, but any other value is equal 0 (zero).
I'm not down-voting.

Mehdi Gholam 27-Aug-12 5:45am

Ahh! I believe you are right! weird design!

Maciej Los 27-Aug-12 5:55am

Would you like to correct or delete this answer?

Mehdi Gholam 27-Aug-12 5:56am

Edited thanks

Maciej Los 27-Aug-12 5:58am

God answer, my 5!

Mehdi Gholam 27-Aug-12 6:03am

Cheers losmac!

Santhosh Kumar Jayaraman · Answer 2 · 2012-08-26T23:20:00

Solution 2

You have to use pivot.
Check this article. Similar kind of table structure

Pivot two or more columns in SQL Server 2005[^]

Posted 26-Aug-12 23:20pm

Santhosh Kumar Jayaraman

Comments

Maciej Los 27-Aug-12 5:41am

Good link, +5!

Kuthuparakkal · Answer 3 · 2012-08-26T23:30:00

Solution 4

This is a tricky solution, we check to see if valid (ie equals P) then Concatenate string and wehen NULL use an empty string. And we replace any 'A' comin in to empty string.

Suppose you have 5 Presents, you get PPPPP, now you take length of the this, you get how many P's as they all are single character.

SQL

SELECT 
Mon
, year
, Emp_Id
, LEN(REPLACE(COALESCE(D1, '') + COALESCE(D2, '') + COALESCE(D3, '') +... +COALESCE(D30, ''),'A',''))  
FROM yourTable

Thanks,

Kuthuparakkal

Posted 26-Aug-12 23:30pm

Kuthuparakkal

Updated 26-Aug-12 23:41pm

v4

Comments

Maciej Los 27-Aug-12 5:37am

D1 to D30 can store values A, P, NULL. This is not good solution. Try better, i'm not down-voting.

Kuthuparakkal 27-Aug-12 5:40am

Updated solution, that caters 'A' too now.

Maciej Los 27-Aug-12 5:43am

Good work! +5

Maciej Los 27-Aug-12 5:52am

By the way...
Do we need to use COALESCE([FieldName],'') for each Dx?
I'm almost sure that the query:

SELECT Mon, year, Emp_Id , LEN(REPLACE(COALESCE(D1 + D2 + D3 ...,''),'A',''))  
FROM yourTable

should works too.

Kuthuparakkal 27-Aug-12 5:55am

NULL + Anything OR Anything + NULL is always NULL... D1+D2+..+Dx will become NULL if any Di is NULL

Kuthuparakkal 27-Aug-12 5:50am

Thanks Iosmac

Maciej Los 27-Aug-12 5:53am

Use "Reply" widget to put reply. ;)

Kuthuparakkal 27-Aug-12 5:55am

sure!

Kuthuparakkal · Answer 4 · 2012-08-26T23:24:00

Solution 3

SQL

SELECT 
  Mon
, year
, Emp_Id
, CASE WHEN (D1='P') THEN 1 ELSE 0 END + CASE WHEN (D2='P') THEN 1 ELSE 0 END + 
  CASE WHEN (D3='P') THEN 1 ELSE 0 END + CASE WHEN (D4='P') THEN 1 ELSE 0 END + 
  .... +
  .... +
  CASE WHEN (D29='P') THEN 1 ELSE 0 END + CASE WHEN (D30='P') THEN 1 ELSE 0 END 
FROM yourTable

Thanks,

Kuthuparakkal

Posted 26-Aug-12 23:24pm

Kuthuparakkal

Updated 26-Aug-12 23:30pm

v2

Comments

Kuthuparakkal 27-Aug-12 5:34am

any one wants to downvote include the comments too, that's elegant way doing this. But if you are a mfker keep vote without comments, i dont complain.

Maciej Los 27-Aug-12 5:35am

It's not optimal, but should works! +4

Kuthuparakkal 27-Aug-12 5:37am

Thanks Iosmac, much appreciated

How to count data of a single row in sql server

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

How to count data of a single row in sql server

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us