Avatar of rckrch
rckrch
Flag for United States of America asked on

SQL Aggregate functions

I am trying to figure out an SQL query to get aggregate calculations from a data table.  The data table is a simple list of dates and data (real).  I am simply trying to get the AVG, MAX, and MIN of the top 100 data points in descending order.  There are several thousand data points in the table.  The Select statement I have used that does not work is:

SELECT TOP 100 Cur_Date, AVG(DataMean) AS DMEAN, MAX(DataMean) AS DMAX, MIN(DataMean) AS DMIN  FROM DataTable WHERE PartNumber = ? ORDER BY Cur_Date DESC.

Can anyone help?

Thanks,
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
lcohan

"The Select statement I have used that does not work is:" - and what actually does not work because the statement as you put it above is valid from syntax point of view and assuming the DataMean has values that can be used in the functions you mentioned above.
Maybe you're missing a GROUP BY some_column criteria for which you want to calculate the aggregated values?
Please see the link below for basic aggregations concept/queries and hopefully they help you solve your problem.
https://www.zentut.com/sql-tutorial/sql-aggregate-functions/
rckrch

ASKER
The DataTable definitely has over 1500 rows of data.  I tried using the Group By function, but I can't figure out how to use it with the top 100 select statement.  I only want to use the last 100 rows for the aggregate functions.  The below query gives me the listing of 100 rows, but not a single calculation of the aggregates for the last 100 DataMean rows.  I am looking for a single row in the query result that gives me the aggregate calculations for the top 100 rows of the database.

SELECT TOP 100 Cur_Date, MetricName, DieNumber, AVG(DataMean) AS DMEAN, COUNT(DataMean) AS DCOUNT
  FROM [DataTable]
  WHERE DieNumber='38.11402' AND MetricName='CLD a2'
  GROUP BY Cur_Date, MetricName, DieNumber
  ORDER BY MetricName DESC
lcohan

I think we can do that by using an intermediate record set as per query below:
WITH cte_die_amounts (Cur_Date, MetricName, DieNumber, DMEAN, DCOUNT) AS 
(
	SELECT Cur_Date, MetricName, DieNumber, AVG(DataMean) AS DMEAN, COUNT(DataMean) AS DCOUNT
	  FROM [DataTable]
		WHERE DieNumber='38.11402' AND MetricName='CLD a2'
		GROUP BY Cur_Date, MetricName, DieNumber
  
)

SELECT TOP 100 Cur_Date, MetricName, DieNumber, DMEAN, DCOUNT
FROM 
    cte_die_amounts
ORDER BY MetricName DESC;

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
rckrch

ASKER
I tried this, but I get the same result.  this lists the last top 100 rows.  Not a single row showing the aggregate calculations for the top 100 rows.  I also replaced the Order By MetricName with Order By Cur_Date.   The top 100 rows should be based on the latest dates in the database.
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rckrch

ASKER
Thanks Scott!!
Perfect, this works.
PortletPaul

Extending Scott's suggestion a tad (which I believe points to the solution) you need to use ORDER BY with TOP then do the calculations (I think)

SELECT
    Cur_Date
  , AVG( DataMean ) AS DMEAN
  , MAX( DataMean ) AS DMAX
  , MIN( DataMean ) AS DMIN
FROM (
    SELECT TOP (100)
        Cur_Date
      , DataMean
    FROM DataTable
    WHERE PartNumber = 1
    ORDER BY
        DataMean DESC
) AS derived
GROUP BY
    Cur_Date
ORDER BY
    Cur_Date DESC
;

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

@PortletPaul:

You're quite right if the date is relevant to the calc.

But I can't believe it is.  What would a mean from a single day's activity really be worth?!
rckrch

ASKER
It is not a mean from a single day's activity.  It is a statistic representing 100 dates. Thanks PortletPaul for the clarification.
rckrch

ASKER
PortletPaul - That solution does not give me the single line of aggregate calculations - just of list of the top 100 lines from data base.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PortletPaul

Sorry folks I added the comment before the solution was chosen. It was only intended as a possible clarification the core was what Scott added anyway.