0

how to calculate aggregate function using mysql database.The following query returns invalid use of group functions.

select avg(max(qty)) from tb_name.
Improve this question
5
  • Your question in the current form is ambiguous. Please add sample data and expected output. Commented Mar 19, 2014 at 9:32
  • max will return only one result and you want to take average of max qty, how is it possible Commented Mar 19, 2014 at 9:33
  • Use only one function ..either AVG or MAX.... SELECT AVG(qty) FROM tb_name Commented Mar 19, 2014 at 9:41
  • ok sir whether is it possible to calculate max(avg(qt))@AzizShaikh @Viswanath polki@Revanayya hiremath Commented Mar 19, 2014 at 9:51
  • no it wont, you must try any other way of storing sum of max qty(ies) and number of categories in which you are finding max qty and then divide them Commented Mar 19, 2014 at 10:10

3 Answers 3

Reset to default
2

You do not have any groups in your statement and if you just read it like this you should understand why it is not yielding a sensible result:

You say Give me the average of the maximum value for qty from tb_name, but since you are not grouping by anything there is only one maximum value.

If you need both values in one query, you can do so, but you need to have a grouping on your query for example like this:

select avg(qty), max(qty) from tb_name group by SomethingOtherThanQty

From your comments I guess you are indeed looking for a maximum of all averages over some group, for that a query with subquery could look like this:

SELECT MAX(AVERAGE) FROM
    (SELECT
            AVG(qty) AS AVERAGE
        FROM tb_name
        GROUP BY YourGroupingCriteria) AS SubQuery
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

sir is it possible to calculate like this max(avg(qty)) it shows invalid use of group
@subramani I added one example of how I can imagine a reasonable query to do so, but since I do not know your desired result or your underlying table structure I can only guess ;)
no sir i need to calculate in single column itself for example i need to calculate (max for the avg) function like this max(avg(qty)) whether is it possible. if it is not so pls tell me reason@ DrCopyPaste
@subramani you need to add more requirements as to what you need exactly, the maximum of the average is equal to the average if you only have one group, I imagine you can do a subquery in that you fetch average values of some GROUP and run a query on that that returns the maximum average of that subquery, but again what group are we talking about?
maximum of average in not equal to average sir if it have different id .for example id col contains 1 2 2 3 and qty col contains 10 20 30 40 . now i need to calculate max(avg(qty)) group by id. which may calculate avg and then max for each id ... sir but this query is not working..@DrCopyPaste
|
1

You can do the following:

SELECT AVG(qty) FROM tb_name

This will give you the average value of quantity

OR

SELECT MAX(qty) FROM tb_name

This will give you the maximum value of quanity

Using both AVG and MAX on the same table is both logically and syntax-wise incorrect.

Improve this answer

1 Comment

i need to calculate both in single query like max(avg(qty)) but it shows invalid
0

try this

SELECT AVG(SELECT MAX(qty) FROM tb_name) FROM tb_name
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.