SQL Query to join 2 tables and get max values

1

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
) ON [PRIMARY]

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

2

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
FROM (
    SELECT R.ChannelID, COUNT(*) AS [count],
    MAX(R.Reading) AS max_reading, 
    MIN(R.Reading) AS min_reading, 
    AVG(R.Reading) AS avg_reading, 
    R.MappingID
    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
ORDER BY DT.ChannelID