Calculating Min, Max and Average values from pre-determined groups of data

Question

Post reply

Calculating Min, Max and Average values from pre-determined groups of data

Weegee71

SSChasing Mays

Points: 604
More actions
October 5, 2021 at 12:12 pm

#3936608

Hi there
I have a table of readings which can vary in the number of records which can be anything from 500 to 80,000
The DDL to create and populate this is as follows:
If OBJECT_ID(N'tempdb..#SIData', N'U') IS NOT NULL DROP TABLE #SIData
CREATE TABLE [#SIData](
[ReadingDateTime] [datetime2](7) NULL,
[ReadingValue] [float] NULL
) ON [PRIMARY]
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:21:58.7500000' AS DateTime2), 67.88)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:23:58.7500000' AS DateTime2), 67.88)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:25:58.7500000' AS DateTime2), 67.87)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:27:58.7500000' AS DateTime2), 67.91)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:29:58.7500000' AS DateTime2), 67.89)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:31:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:33:58.7500000' AS DateTime2), 67.91)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:35:58.7500000' AS DateTime2), 67.91)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:37:58.7500000' AS DateTime2), 67.84)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:39:58.7500000' AS DateTime2), 67.87)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:41:58.7500000' AS DateTime2), 67.85)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:43:58.7500000' AS DateTime2), 67.85)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:45:58.7500000' AS DateTime2), 67.88)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:47:58.7500000' AS DateTime2), 67.88)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:49:58.7500000' AS DateTime2), 67.89)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:51:58.7500000' AS DateTime2), 67.84)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:53:58.7500000' AS DateTime2), 67.91)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:55:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:57:58.7500000' AS DateTime2), 67.87)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T03:59:58.7500000' AS DateTime2), 67.86)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:01:58.7500000' AS DateTime2), 67.87)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:03:58.7500000' AS DateTime2), 67.87)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:05:58.7500000' AS DateTime2), 67.84)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:07:58.7500000' AS DateTime2), 67.86)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:09:58.7500000' AS DateTime2), 67.86)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:11:58.7500000' AS DateTime2), 67.84)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:13:58.7500000' AS DateTime2), 67.85)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:15:58.7500000' AS DateTime2), 67.86)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:17:58.7500000' AS DateTime2), 67.85)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:19:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:21:58.7500000' AS DateTime2), 67.85)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:23:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:25:58.7500000' AS DateTime2), 67.84)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:27:58.7500000' AS DateTime2), 67.83)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:29:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:31:58.7500000' AS DateTime2), 67.84)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:33:58.7500000' AS DateTime2), 67.93)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:35:58.7500000' AS DateTime2), 67.88)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:37:58.7500000' AS DateTime2), 67.83)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:39:58.7500000' AS DateTime2), 67.81)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:41:58.7500000' AS DateTime2), 67.8)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:43:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:45:58.7500000' AS DateTime2), 67.79)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:47:58.7500000' AS DateTime2), 67.81)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:49:58.7500000' AS DateTime2), 67.82)
GO
INSERT [#SIData] ([ReadingDateTime], [ReadingValue]) VALUES (CAST(N'2021-09-17T04:51:58.7500000' AS DateTime2), 67.78)
GO
Now I want to be able to split this up as follows:
1) Take the total group of records and divide them into chunks which are of a pre-defined number (say for example 800)
So for example, if I had a total of 80,000 records then I would extracting 100 groups of 800 records each
2) Then withing each of those 100 chunks, I would summarise the data as folllows:
I tried writing the following statement to calculate this but it didnt work
SELECT Min(ReadingValue) as MinValue, Max(ReadingValue) as MaxValue , Avg(ReadingValue), Thread = NTILE(800) OVER (ORDER BY ReadingValue)
FROM #SIData
group by ReadingValue;
Can anyone help me here please

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

NTILE specifies the number of groups, not the size of each group. So in your example it should be 100 not 800.

For the sample data shown, 10 groups makes more sense. That would look like this:

;WITH cte_ntile AS (
    SELECT *, NTILE(10) OVER(ORDER BY ReadingValue) AS [Group]
    FROM #SIData
)
SELECT MIN(ReadingValue) AS MinValue, MAX(ReadingValue) AS MaxValue, AVG(ReadingValue) AS AvgValue, [Group]
FROM cte_ntile
GROUP BY [Group]
ORDER BY [Group]

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Hi Scott

That worked very well for me. Thank you very much.

I re-wrote that query in Dyamic SQL. I work out the recordcount and then divide that by 800 , to get the number of groups

which i pass into NTILE.