[T-SQL] Get sum of averages - a challenge

---

Get sum of averages - a challenge Posted: 29 Mar 2013 12:41 AM PDT I have a need for a query that groups data, returns averages for the groups, but also sums those averages. This has been stumping me...A simplified version of the table isCREATE TABLE table_1 (Curr_date DATE, Action CHAR(15), Duration FLOAT)Data is likeINSERT Table_1 VALUES ('2013-03-29','SignOn',1000.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',450.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',900.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',300.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',1100.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',475.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',950.1234);INSERT Table_1 VALUES ('2013-03-29','GetBal',320.1234);INSERT Table_1 VALUES ('2013-03-29','SignOn',800.1234);I can get the average action durations like thisSELECT Curr_date, Action, AVG(Duration)/1000 as Avg_DurationFROM Table_1WHERE Curr_date = CAST(GETDATE() as DATE) GROUP BY Curr_date, Action[font="Courier New"]Curr_date Action Avg_Duration2013-03-29 GetBal 0.38637342013-03-29 SignOn 0.9501234[/font]But I also need to sum those averages for both actions together. So for SignOn and GetBal together the sum is about 1.34. Anyone have any good suggestions for that? Thanks!

How to show Carriage Return Char(13) in XML as real CR instead of tag. Posted: 28 Mar 2013 08:22 AM PDT Hi,I'm using a neat query to concatenate strings. It is using XML to do.I had 200.000 rows to be concatenated and the standard SQL was stalling.This statement is doing it in 0.4 seconds!! Amazing!One downside is dat the carriage return CHAR(13) is added as a tag being [b] [/b].I'm looking for a way to have it as a real CR.Anyone knows a way to do this?This is my test code:[code="sql"]DECLARE @TABLE TABLE (ID INT IDENTITY(1,1), LINE VARCHAR(100))DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)DECLARE @OUTPUT VARCHAR(MAX) = ''INSERT @TABLEVALUES ('ONE'),('TWO'),('THREE'),('FOUR'),('FIVE'),('SIX')SET @OUTPUT = (SELECT CAST( LINE AS VARCHAR(MAX) ) + @CRLF FROM @TABLE ORDER BY ID FOR XML PATH( '' ))SELECT @OUTPUT[/code]

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610