Filter a SUM column? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Filter a SUM column?

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-11-11 : 10:38:13

Hi,

I have the following table structure:

CREATE TABLE [dbo].[ActionLogTEST] (
[ActionLogID] [int] IDENTITY (1, 1) NOT NULL ,
[AgentID] [int] NULL ,
[ActionDuration] [float] NULL ,
[CampaignID] [int] NULL ,
[ActionID] [int] NULL ,
[SessionID] [int] NULL ,
[ActionStartDate] [datetime] NULL ,
[ActionStopDate] [datetime] NULL
) ON [PRIMARY]

INSERT INTO dbo.ActionLogTEST
SELECT 176, 0.093753, 27, 11, 1, 10/11/2006, 10/11/2006
UNION ALL
SELECT 176, 1.6719285, 27, 11, 1, 10/11/2006, 10/11/2006
UNION ALL
SELECT 176, 24.844545, 27, 8, 1, 10/11/2006, 10/11/2006
UNION ALL
SELECT 176, 26.6102265, 27, 2, 1, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 1.0781043, 34, 19, 2, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 193.3087884, 34, 11, 2, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 145.2315865, 34, 2, 2, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 339.4622322, 34, 10, 2, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 0.2343705, 34, 19, 2, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 278.6040257, 34, 2, 2, 10/11/2006, 10/11/2006
UNION ALL
SELECT 957, 1.7030923, 34, 13, 2, 10/11/2006, 10/11/2006

If I run the following:

SELECT DISTINCT SUM(ActionDuration) [Totals], AgentID, ActionID, CampaignID, CONVERT(Varchar,GETDATE()-1,112)
FROM ActionLogTest
WHERE ActionID IN (11,2)
GROUP BY AgentID, CampaignID, ActionID
ORDER BY AgentID, CampaignID

I get:
Totals AgentID ActionID CampaignID
26.6102265 176 2 27 20061110
1.7656815 176 11 27 20061110
423.8356122 957 2 34 20061110
193.3087884 957 11 34 20061110

(4 row(s) affected)

What I actually want is this - Instead of 1 SUM column for the ActionDuration, I want 2 SUM columns, one for the SUM of ActionDuration where = 2(Total1) and one for the SUM of ActionDuration where = 11(Total2).

So the additional SUM columnsin the result would be :
(Total1) SUM of all action duration that = 2
(Total2) SUM of all action duration that = 11

How can I achieve this please?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-11 : 10:50:40

[code]
SELECT DISTINCT SUM(ActionDuration) [Totals], Sum(Case When ActionID = 2 Then ActionDuration Else 0 End ) As Action2,
Sum(Case When ActionID=11 Then ActionDuration Else 0 End) As Action11,
AgentID, ActionID, CampaignID, CONVERT(Varchar,GETDATE()-1,112)
FROM ActionLogTest
WHERE ActionID IN (11,2)
GROUP BY AgentID, CampaignID, ActionID
ORDER BY AgentID, CampaignID
[/code]

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources