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'
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
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
Expr1000 and Totals
The totals is correct, but not sure why there is a second column made.
RE: Need help with suming multiple columns
RE: Need help with suming multiple 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
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
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 :)