CODEWITHSUNDEEP
sql
user3127986
user3127986

Reputation: 388

lowest amount below the average amoutn in SQL

I am trying to get the lowest amount below an average in SQL

I have amount 2000, 2500, 3000. The average is 2500.

I want to build an SQL query to calculate the AVG and to extract the lowest amount from it.

SELECT AVG(Amount) FROM CONTRACT....

I can't figure out how to do the rest

Thanks

Upvotes: 0

Views: 57

Answers (3)

Suyash Khandwe
Suyash Khandwe

Reputation: 396

Assuming that you are concerned only about the amount field and that the data is not very huge you could try this out -

SELECT MIN(AMOUNT) FROM CONTRACT WHERE AMOUNT <= (SELECT AVG(AMOUNT) FROM CONTRACT)

But, wouldn't the lowest amount below average would simply be the lowest amount of all? Something like this -

SELECT MIN(AMOUNT) FROM CONTRACT

Upvotes: 0

Daniel C
Daniel C

Reputation: 1362

I think what you're looking for is simply:

SELECT MIN(Amount) FROM Contract

But your question implies somehow applying AVG to a subset of your data, which I don't really understand.

Upvotes: 3

Philip Kelley
Philip Kelley

Reputation: 40319

Do you mean subtract the lowest amount from the average? If so, that'd be

SELECT AVG(Amount) - MIN(Amount) FROM CONTRACT...

Upvotes: 0

Related Questions