Solved

# How can  I sum a column as an expression?

Posted on 2010-11-15
931 Views
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.
0
Question by:rhservan
• 2

LVL 13

Expert Comment

ID: 34141798
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 Comment

ID: 34142232
Sameer,

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

LVL 21

Assisted Solution

Alfred1 earned 250 total points
ID: 34142304
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

LVL 13

Accepted Solution

sameer2010 earned 250 total points
ID: 34160681
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question