CODEWITHSUNDEEP
sql
Crowntree
Crowntree

Reputation: 13

Can I avoid the aggregate when using a group by?

I am looking to join one column from one table to another. However, the only common columns are the timestamp and ID; these contain multiple of the same date/time or ID as there are multiple ID's per 10 minute period. I understand this creates an issue with joining the tables due to a many to many relationship. I can get the data to work with the code below aside from the tg.value2 column as it won't work without the aggregate due to the group by. But I need the group by to prevent the multiplying of rows.

Only work around I can see is to add the aggregate to tg.value2, however I don't want this. I need the data in that column to simply appear as it is without any aggregates, joining to the TimeStamp and ID from tbl1 and producing the corresponding value.

    SELECT
       t.TimeStamp,
       t.ID,
       AVG(t.value1) AS AvgVal1,
       AVG(tg.value2) AS AvgVal2
    FROM [dbo].[tbl1] as t
    INNER JOIN [dbo].[tbl2] AS tg ON t.TimeStamp = tg.TimeStamp
    GROUP BY t.TimeStamp, t.ID
    ORDER BY t.TimeStamp desc, t.ID

Any help would be greatly appreciated.

Thanks

Gordon: Image below shows the 2 tables and the desired output.

https://i.sstatic.net/JY0bh.png

Thank you

Upvotes: 1

Views: 78

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You seem to want to join on both timestamp and id:

SELECT t.TimeStamp, t.ID, t.value1, tg.value2
FROM [dbo].[tbl1] t JOIN
     [dbo].[tbl2] tg
      ON t.TimeStamp = tg.TimeStamp AND t.ID = tg.ID
ORDER BY t.TimeStamp desc, t.ID

Upvotes: 1

Zeeshan Arif
Zeeshan Arif

Reputation: 499

try this:

SELECT
   t.TimeStamp,
   t.ID,
   t.value1 AS AvgVal1,
   tg.value2 AS AvgVal2
FROM [dbo].[tbl1] as t
INNER JOIN [dbo].[tbl2] AS tg ON t.TimeStamp = tg.TimeStamp
GROUP BY t.TimeStamp, t.ID, t.value1, tg.value2
ORDER BY t.TimeStamp desc, t.ID

Upvotes: 0

Related Questions