Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table called TimeSheet :

Card_No    Name    1    2    3    4
123        GGG    A    P    P    P
123        GGG    A    P    A    P
123        GGG    A    P    P    P
123        GGG    A    A    A    P

I want to count total A in one row. Result should be like this

Card_No    Name    1    2    3    4    TotalA
123         GGG    A    P    P    P    1
123         GGG    A    P    A    P    2
123         GGG    A    P    P    P    1
123         GGG    A    A    A    P    3

How can I do it ? Please help me.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

One way

SELECT 
      *,
      CASE WHEN [1] = 'A' THEN 1 ELSE 0 END + 
      CASE WHEN [2] = 'A' THEN 1 ELSE 0 END + 
      CASE WHEN [3] = 'A' THEN 1 ELSE 0 END + 
      CASE WHEN [4] = 'A' THEN 1 ELSE 0 END AS TotalA
FROM TimeSheet

or

SELECT *,
       (SELECT count(*)
        FROM   (VALUES([1]),
                      ([2]),
                      ([3]),
                      ([4])) V(c)
        WHERE  c = 'A') AS TotalA
FROM   TimeSheet 
share|improve this answer
    
When i use the first one Its Count the next row A also in same row why ? –  sandip Jan 5 '13 at 13:36
1  
@sandip - You must be mistaken. Both return the same results. SQL Fiddle SQL Server 2008 has no functionality to reference the next row in a SELECT (In SQL Server 2012 LAG/LEAD can do this). Maybe you made an error applying it to your actual table/columns feel free to add a screenshot demonstrating the issue. –  Martin Smith Jan 5 '13 at 13:41
    
Thank you it's work. –  sandip Jan 6 '13 at 7:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.