rckrch

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,

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,

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

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;
```

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks Scott!!

Perfect, this works.

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
;
```

@PortletPaul:

You're quite right

But I can't believe it is. What would a

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?!ASKER

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

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.

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/