SQLTeam.com | Weblogs | Forums

SQL Query to join 2 tables and get max values



SELECT Readings.ChannelID, COUNT(*) AS count,
MAX(Readings.Reading) AS max_reading,
MIN(Readings.Reading) AS min_reading,
AVG(Readings.Reading) AS avg_reading, Readings.MappingID
FROM Readings
where mappingid = 18
GROUP BY Readings.ChannelID, Readings.MappingID
ORDER BY Readings.ChannelID

If I use the above query it returns what I need from my database, I want to join another table with the ChannelCaption, when I do this it doubles the count value.

My Readings Table
CREATE TABLE [dbo].[Readings](
[RecNo] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[MappingID] [smallint] NOT NULL,
[ChannelID] [smallint] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Reading] [smallint] NOT NULL,
[ReadingAttribute] varchar NULL

My table where the caption is
CREATE TABLE [dbo].[ChannelSetup](
[MapID] [smallint] NULL,
[ChannelID] [smallint] NULL,
[Caption] varchar NULL


Wait to do the join until after the main query has completed. To accomplish that, add an outer query to do the Caption look, which makes the main query a derived table.

SELECT DT.*, CS.Caption
    SELECT R.ChannelID, COUNT(*) AS [count],
    MAX(R.Reading) AS max_reading, 
    MIN(R.Reading) AS min_reading, 
    AVG(R.Reading) AS avg_reading, 
    FROM dbo.Readings R
    WHERE R.mappingid = 18
    GROUP BY R.ChannelID, R.MappingID
) AS DT --dt=derived_table
LEFT OUTER JOIN dbo.ChannelSetup CS ON CS.MapID = DT.MappingID AND 
    CS.ChannelID = DT.ChannelID