SQL Sum with multiple columns 1

[jontout]

Hi Folks.

I have a table with 10 columns and each column could contain 1 of 3 possible enteries.

I've got a query that will sum 1 column and I'm trying to get my head around how to sum the other 9 columns, in 1 query.

Here's the code, thanks in advance for any help.

SELECT tblVotes.Res1, Sum(IIf(Res1="0",1,0)) AS [FOR], Sum(IIf(Res1="1",1,0)) AS AGAINST, Sum(IIf(Res1="2",1,0)) AS ABSTAIN
FROM tblVotes
GROUP BY tblVotes.Res1
ORDER BY tblVotes.Res1;

Cheers,

Jon

 

[lespaul]

I'm guessing that you have Res2, Res3, Res4, etc? And these are resolutions, maybe?

Try this, create a query that normalizes your data:

SELECT "Res1" As Resolution, Res1 As Position FROM tblVotes
UNION
SELECT "Res2", Res2 FROM TblVotes
UNION
...
SELECT "Res10", Res10 From TblVotes

Now you can do this:

SELECT Resolution, IIF(Position = "0", "For", iif(position = "1", "Against","Abstain")) As Position, Count(*) As Votes FROM qryNormal GROUP BY Resolution, IIF(Position = "0", "For", iif(position = "1", "Against","Abstain"))

and your results will be:
[tt]
Resolution Position Votes
Res1 For 10
Res1 Against 2
Res1 Abstain 1
Res2 For 16
Res2 Against 4
Res2 Abstain 1
...[/tt]


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[jontout]

That's brilliant, Thanks Leslie I'll give it a go.
They are resolutions by the way...

Cheers,

Jon

 

[jontout]

Yep, that took me a few minutes to work out.
I've not used Union before so I fiddled a bit to try to understand what it does and the query works, great stuff.

 

[lespaul]

glad to help! thanks for the star,
Leslie