# 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.
###### Who is Participating?

Commented:
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
``````
0

Commented:
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
``````
0

Author Commented:
Sameer,

There seems to be some missing information on line five at the end.
0

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
``````
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.