CODEWITHSUNDEEP
sqlsql-serverms-access
Jota André
Jota André

Reputation: 5

Get the MAX, MIN, AVG between two DATES

I'm trying to query min, max and avg between two dates, that I got from [DATA].

SELECT
    MIN([INDEX]) as MIN,
    AVG([INDEX]) as avg,
    MAX([INDEX]) as max
  FROM [data].[dbo].[db_DATA]

I tried with the following sql query, but didn't work too.

SELECT
    MIN([INDEX]) as MIN,
    AVG([INDEX]) as avg,
    MAX([INDEX]) as max
  FROM [data].[dbo].[db_DATA]
 having MIN([INDEX]) between '2017-02-20' AND '2017-02-25'

Upvotes: 0

Views: 173

Answers (2)

Livius
Livius

Reputation: 956

As you do not GROUP BY put it in the where clause instead of HAVING. Also remove MIN() because if min is between then max is also and avg is also.

SELECT
    MIN([INDEX]) as MIN,
    AVG([INDEX]) as avg,
    MAX([INDEX]) as max
  FROM [data].[dbo].[db_DATA]
 WHERE [INDEX] between '2017-02-20' AND '2017-02-25'

Upvotes: 2

Tarun. P
Tarun. P

Reputation: 442

the dates is always with '#' if you are working with Ms-access

try like this

SELECT
    MIN([INDEX]) as MIN,
    AVG([INDEX]) as avg,
    MAX([INDEX]) as max
  FROM [data].[dbo].[db_DATA]
 having MIN([INDEX]) between #02/20/2017# AND #02/25/2017#

Upvotes: 1

Related Questions