Search code examples
mysqlsqlaggregate-functionsmysql-error-1111

MySQL select count(*) statement excluding minimium value

I have a simple MySQL select statement:

SELECT COUNT(*) AS COUNT FROM MY_TABLE WHERE TIME_STAMP > ?

But I do not want to count the first/earliest row if the TIME_STAMP for that earliest row is greater than my WHERE threshold. Something like:

SELECT COUNT(*) AS COUNT FROM MY_TABLE WHERE TIME_STAMP > ? AND TIME_STAMP > $earliest

Obviously I can get the earliest TIME_STAMP with an additional prior query. But I was wandering if I can do it all in one. I tried this but throws an "Invalid use of group function" error:

SELECT COUNT(*) AS COUNT FROM MY_TABLE WHERE TIME_STAMP > ? AND TIME_STAMP > MIN(TIME_STAMP)

Any suggestions? Thanks

Solution

  • Your last query would work if you got the MIN value from a nested select. Group functions need context, of what do you want the minimum?

    SELECT COUNT(*) AS COUNT FROM MY_TABLE WHERE TIME_STAMP > ? AND TIME_STAMP > (SELECT   MIN(TIME_STAMP) FROM MY_TABLE)