We help IT Professionals succeed at work.

SQL Aggregate functions

rckrch
rckrch asked
on
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,
Comment
Watch Question

View Solution Only

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
"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

Author

Commented:
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
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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

rckrch

Author

Commented:
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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
>> I am simply trying to get the AVG, MAX, and MIN of the top 100 data points in descending order. <<

In descending order by what?  Date?  Value?   Maybe you need to get the top 100 highest values rather than last 100 added?

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


rckrch

Author

Commented:
Thanks Scott!!
Perfect, this works.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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
;
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
@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

Author

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

Author

Commented:
PortletPaul - That solution does not give me the single line of aggregate calculations - just of list of the top 100 lines from data base.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.