SQL Server aggregate function query error

Question

My query

SELECT TOP 1 *, COUNT(*) AS totalRun 
FROM history 
ORDER BY starttime DESC`

Estimated outcome is all the data from 1 row in the history table with the latest starttime and a fieldtotalrun with the total amount of records, but... I get the following error.

Msg 8120, Level 16, State 1, Line 1
Column 'history.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What am I doing wrong?

EDIT
example of the result: These are all the fields of the row with the latest starttime in the history table with the extra COUNT field 'totalRun'

Answer 1

Aggregates can only be expressed in two cases.

1. Where you have a GROUP BY statement
2. Where you use the OVER clause

The following will give you the most recent start time and the number of rows in your source table that share that start time...

SELECT
  starttime,
  COUNT(*)  AS row_count
FROM
  history
GROUP BY
  starttime
ORDER BY
  starttime DESC

In this structure the only fields you can select are the ones in the GROUP BY statement (and you can have several), or aggregates *(such as SUM(), COUNT(), etc).

If, however, you want the COUNT(*) to be done over the whole table, and not just the rows grouped together, you can use the OVER clause in the SELECT statement.

SELECT
  *,
  COUNT(*) OVER (PARTITION BY 1) AS row_count
FROM
  history
ORDER BY
  starttime DESC

Because this doesn't use a GROUP BY, you can then also select * rather than just teh fields you are grouping by.

If you need something different, please could you include some example data and the results you would desire?

Answer 2

You either aggregate or group by a column. You have columns that are neither

SELECT TOP 1
   starttime, COUNT(*) AS totalRun
FROM history
GROUP BY starttime, foo
ORDER BY starttime DESC;

If you need a column foo, then add it as follows

SELECT TOP 1
   starttime, foo, COUNT(*) AS totalRun
FROM history
GROUP BY starttime, foo
ORDER BY starttime DESC, foo;

Answer 3

I could not unerstand the requirement properly. Why you are using top 1 with count(*) and without the group by clause.

If you want the result TotalRuns earned on last date then you can use this query

SELECT TOP 1  starttime, COUNT(1) AS totalRun 
            FROM history Group by starttime ORDER BY starttime DESC

Answer 4

If this is the requirement:

Estimated outcome is all the data from 1 row in the history table with the latest starttime and a fieldtotalrun with the total amount of records,

select top 1 * from (Select * from history where starttime= (select max(starttime) from history) )i full outer join (select count(1) count , max(starttime) sttime as fieldtotalrun from history ) j on i.starttime=j.sttime