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]