Solved

How can  I sum a column as an expression?

Posted on 2010-11-15
4
909 Views
Last Modified: 2012-05-10
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
Comment
Question by:rhservan
  • 2
4 Comments
 
LVL 13

Expert Comment

by:sameer2010
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

Open in new window

0
 

Author Comment

by:rhservan
ID: 34142232
Sameer,

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

Assisted Solution

by:Alfred1
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

Open in new window

0
 
LVL 13

Accepted Solution

by:
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

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now