How can I sum a column as an expression?

In my VarChar Column,  'ZZ',  I have 3  possibilities with a total of 134 rows :

Possibilities
1.  aaaa
2.  bbbb
3.  cccc

I need to create a table to show a total of each of these possibilities that are listed.

Example:
aaaa    bbbb    cccc
 53         47        34        TOTAL
`
Please provide an expression that I can use to obtain each of  the 3  totals.
rhservanAsked:
Who is Participating?
 
sameer2010Commented:
Are you getting any error? What is the error you are getting?
The first 4 lines select the count of non-blank possiblities and 5th row calculates the sum for all non-blank rows. If you do not need total for all possibilities, you can drop that row. So, the query would become
select 
sum(case when possibility = 'aaa' then 1 else 0 end) possaaa,
sum(case when possibility = 'bbb' then 1 else 0 end) possbbb,
sum(case when possibility = 'ccc' then 1 else 0 end) possccc
from your_table

Open in new window

0
 
sameer2010Commented:
Try this
select 
sum(case when possibility = 'aaa' then 1 else 0 end) possaaa,
sum(case when possibility = 'bbb' then 1 else 0 end) possbbb,
sum(case when possibility = 'ccc' then 1 else 0 end) possccc,
sum(case when possibility = 'aaa' then 1 else 0 end) + sum(case when possibility = 'bbb' then 1 else 0 end) + sum(case when possibility = 'ccc' then 1 else 0 end) posstotal
from your_table

Open in new window

0
 
rhservanAuthor Commented:
Sameer,

There seems to be some missing information on line five at the end.
0
 
Alfred A.Commented:
Try something like this.
SELECT *
FROM (
SELECT (CASE ZZ
           WHEN 'aaaa' THEN 'aaaa'
           WHEN 'bbbb' THEN 'bbbb'
           WHEN 'cccc' THEN 'cccc'
        END) As [Poss],
        (COUNT((CASE ZZ
           WHEN 'aaaa' THEN 'aaaa'
           WHEN 'bbbb' THEN 'bbbb'
           WHEN 'cccc' THEN 'cccc'
        END)) As [SubTotal]
FROM YOURTABLE
GROUP BY ZZ
) TableZZ
PIVOT (
SUM(SubTotal)
FOR [Poss] IN (
[aaaa],[bbbb],[cccc]
)
) PivotTable

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.