×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Need help with suming multiple columns

Need help with suming multiple columns

Need help with suming multiple columns

(OP)
I have a table with 5 columns that are named Result, Result2, Result3, Result4, and Result5. The values of the columns are either "Won" or "Lose". I want to count all the values in each column that have "won" and sum them to get a total of all the won values.
This is what I have, and it is returning empty values.

SQL Statement:

SELECT (Count(Result)+Count(Result2)+Count(Result3)+Count(Result4)+Count(Result5)) As Total

From February2005

Where Result= 'Won' and Result2='Won' and Result3='Won' and Result4='Won' and Result5='Won'

RE: Need help with suming multiple columns

You received empty because there are not a single row in the table with all "Result*"='Won'. Change the SQL as follows:

SELECT Count(*),
(SELECT Count(Result) from February2005 where result='Won')+
(SELECT Count(Result2) from February2005 where result2='Won')+
(SELECT Count(Result3) from February2005 where result3='Won')+
(SELECT Count(Result4) from February2005 where result4='Won')+
(SELECT Count(Result5) from February2005 where result5='Won')
as TOTAL from February2005

I added "Count(*)" which gives the number of rows processed.
Tested with FirebirdSQL engine and it works.

HTH

RE: Need help with suming multiple columns

(OP)
Well, it worked, but it returned 2 columns. One being:

Expr1000   and    Totals

The totals is correct, but not sure why there is a second column made.

RE: Need help with suming multiple columns

(OP)
Oh did I forget to mention, I am using Access.

RE: Need help with suming multiple columns

The SQL statmeent as provided by rtmpxr is asking for two columns.  Please note the comma between Count(*) and the five count(resultx) clauses.  The comma delineates the two columns.

The first column is un-named, so by default, Access returns its value as Expr1000.  The second column is named (As Totals), and is so returned.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

RE: Need help with suming multiple columns

(OP)
Well I found another way to make it work also...

Thank you for all the help, I appreciate it....

here is my example.

SELECT Count(Result) As Total
From February2005
Where Result='Won'
Union All
SELECT Count(Result2) As Total
From February2005
Where Result2='Won'
Union All
SELECT Count(Result3) As Total
From February2005
Where Result3='Won'
Union All
SELECT Count(Result4) As Total
From February2005
Where Result4='Won'
UNION ALL SELECT Count(Result5) As Total
From February2005
Where Result5='Won';

RE: Need help with suming multiple columns

You can replace "Count(*)" with DISTINCT and you'll obtain just the TOTAL column in a single row.
On Firebird your SQL gives me 5 rows each with the totla on coreso\ponding column. I beleive Access has an own treatement of the UNION ALL clause. But, you know, all is well when it ends well :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources