SQL Aggregate functions

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

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;

Select allOpen in new window

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.

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
;

Select allOpen 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?!

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

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.