help with query / (union / sum)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with query / (union / sum)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-14 : 21:09:32

Hi, I have the following query which returns my results in a UNION which ends up being 2 rows. I'd like to convert it to just bring back 1 row which is a SUM of the unioned rows.

I'm pretty sure my approach here is wrong, but I'm not sure where to go with it. Any help is much appreciated!

So my desired results would be 1 row looking like this

votes_10,votes_15,votes_20,totalVotes
100, 200, 300, 600

Thanks very much!,
mike123



CREATE PROCEDURE [dbo].[select_VoteHistory]  

AS SET NOCOUNT ON 

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',

COUNT(*) AS TotalVotes FROM tblRandomVote  
 
UNION 

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',

COUNT(*) AS TotalVotes FROM tblUserVote

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-14 : 21:47:52

[code]
SELECT sum(votes_10), sum(votes_15), sum(votes_20), sum(TotalVotes )
from
(
SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',

COUNT(*) AS TotalVotes FROM tblRandomVote

UNION

SELECT

ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_10',
ISNULL(SUM ( CASE WHEN points = 10 THEN 1 ELSE 0 END ),0) as 'votes_15',
ISNULL(SUM ( CASE WHEN points = 20 THEN 1 ELSE 0 END ),0) as 'votes_20',

COUNT(*) AS TotalVotes FROM tblUserVote
) a
[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-14 : 22:39:05

thats what I was looking for

thanks once again khtan !! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources