SUM of similar rows - 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 2005 Forums

Transact-SQL (2005)

SUM of similar rows

Author

Topic

k00k
Starting Member

1 Post

Posted - 2009-07-24 : 10:33:31

[code]SELECT
starttime AS Time_Period,
SUM(incalls) AS [Inbound Calls],
SUM(acdcalls) AS [ACD Calls],
SUM(abncalls) AS [Abandoned Calls],
CASE WHEN SUM(incalls) = 0 THEN 0
ELSE 100 * SUM(abncalls) / SUM(incalls) END
AS [% Abandoned],
CASE WHEN SUM(acdcalls) = 0 THEN 0
ELSE (SUM(anstime) / SUM(acdcalls)) END
AS [Avg Speed Answer],
CASE WHEN SUM(abntime) = 0 THEN 0
ELSE (SUM(abntime) / SUM(abncalls)) END
AS [Avg Abandoned Time],
CASE WHEN SUM(incalls) = 0 THEN 0
ELSE ((SUM(ansconncalls1) + SUM(ansconncalls2) + SUM(ansconncalls3) + SUM(ansconncalls4)) / SUM(incalls) * 100) END
AS [% Calls Ans in 30 Sec],
SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6)
AS [Aban calls in 30s],
CASE WHEN SUM(incalls) = 0 THEN 0
ELSE ((SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6)) / SUM(incalls) * 100) END
AS [% Aban in 30s]
FROM hvdn
WHERE
(acd = 1)
AND
(row_date = CONVERT(DATETIME, '2009-07-23 00:00:00', 102))
GROUP BY
starttime
ORDER BY
Time_Period[/code]

The above code DOES work and produces the expected results. A summary of the data returned is displayed below:

My problem is the "similar/duplicate" row at 1000. I need the respective values (while taking into account the existing calculations performed in the query) merged. The desired result is that each half-hourly time period has a single row of data each.

If anyone can help me, I would really, really appreciate it.

I have posted this query on a few discussion forums about SQL so if/when I get an answer, I will update this post accordingly.

I am using SQL Server 2005 Enterprise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:37:15

are you sure starttime is a field in yourtable with values 900,1000,...? i think its a derived column seeing the output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources