Link to home
Start Free TrialLog in
Avatar of rckrch
rckrchFlag 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,
Avatar of lcohan
lcohan
Flag of Canada image

"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/
Avatar of 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
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

Avatar of 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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rckrch

ASKER

Thanks Scott!!
Perfect, this works.
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

@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?!
Avatar of 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.
Avatar of 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.
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.