Need help with suming multiple columns

[oversoul]

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'

 

[rtmpxr]

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

 

[oversoul]

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.

 

[oversoul]

Oh did I forget to mention, I am using Access.

 

[CajunCenturion]

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

 

[oversoul]

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';

 

[rtmpxr]

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