Problem with sql count query

[getnitha]

Hi,

I need a help.I want to get the count of a row.I am using sql server

id fstday secday 3day 4day 5day

aaa P A P A A

bbb A P P A P

In the above result i pasted two rows of data from my table.I want to get the count of each employee having values as 'p' .FOR EG

i want the result set as for userid aaa ------------------2 (Its having 2 P's)

and for userid bbb------------------3 (Its having 3 P's)

plse help me to find this

Regards
Nitha

 

[Stonkie]

The problem you are facing is that the data you want to count are in columns. It should have been put into a seprate table with the day number as a column. With this though, it is still possible to work (little more complicated ).

Try this :

SELECT id, LEN(fstday + secday + 3day + 4day + 5day) - LEN(REPLACE(fstday + secday + 3day + 4day + 5day, 'P', '')) FROM employee

I could not try the code here so you might have to help it a little, but the logic is there.

 

[cjard]

Nitha, please use CODE tags when you want to put pre-formatted data in a post:

id     fstday      secday         3day         4day            5day

aaa     P              A              P              A                  A 

bbb     A              P              P              A                  P

i want the result set as for userid aaa ------------------2 (Its having 2 P's)

and for userid bbb------------------3 (Its having 3 P's)

THis is very hard to understand, can you be more clear?


Stonkie's solution is a good one

 

[Arg81]

THis is very hard to understand, can you be more clear?

I think Nitha is trying to say for every ID that appears (i.e. aaa, bbb) , do a count of the P's that appear for each column.

As Stonkie says, it's not very database friendly and normalised at the moment!

 

[cjard]

I think Nitha is trying to say for every ID that appears (i.e. aaa, bbb) , do a count of the P's that appear for each column.

I agree, but the learning experience here is to get the OP to be more smart about asking questions; he wants free help, gonna have to put the effort in somewhere