Duplicate data when using group & aggregate values

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)

Duplicate data when using group & aggregate values

Author

Topic

palmoswince
Starting Member

3 Posts

Posted - 2010-03-08 : 04:58:12

Hi all,

My SQL query 'skills' are getting a bit rusty after a long break. Tried running a few recommendation in regards to grouping and duplicates but so far havent got it working. The problem I'm having when linking and grouping these tables is duplicates will appear. Appreciate your help in the following.

Table A
DateTime Col1 Col2
01/01/2010 08:00 1 5
01/01/2010 08:00 2 10

Table B
DateTime Col1 Col2
01/01/2010 08:00 3 5
01/01/2010 08:00 4 10
01/01/2010 08:00 5 15

Table C
DateTime Col1 Col2
01/01/2010 08:00 6 50
01/01/2010 08:00 7 100

Hourly datetime will be inserted into these tables. The only relationship/link between these tables are the DateTime. It is also possible that certain intervals will not have any data for these tables. For example at 09:00 there are no data for Table C.

The intended output is to generate a report on hourly/daily/monthly basis where the sample output would be

DateTime Sum(A.Col2) Count(B.Col2) Avg(C.Col2)
01/01/2010 08:00 15 3 75
01/01/2010 09:00 xx x xx
..
01/01/2010 17:00 xx x xx
Total for 01/01 XX XX XX

Thanks!

-JT-

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:27:07

[code]
SELECT a.Datetime,
SumCol2,
COALESCE(CntCol2,0) AS CntCol2,
COALESCE(AvgCol2,0) AS AvgCol2
FROM (SELECT Datetime,SUM(Col2) AS SumCol2
FROM [Table A]
GROUP BY Datetime)a
LEFT JOIN (SELECT Datetime,COUNT(Col2) AS CntCol2
FROM [Table B]
GROUP BY Datetime)b
ON b.Datetime=a.Datetime
LEFT JOIN(SELECT Datetime,AVG(Col2) AS AvgCol2
FROM [Table C]
GROUP BY Datetime)c
ON c.Datetime = b.Datetime
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

palmoswince
Starting Member

3 Posts

Posted - 2010-03-08 : 22:43:07

thanks visakh16!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources