SELECT designation,avg(sales) from emp group by designation having avg(sales)>(SELECT avg(sales) from emp); explain it's working
Question
Explain the following SQL query:
SELECT designation, avg(sales)
FROM emp
GROUP BY designation
HAVING avg(sales) > (SELECT avg(sales) FROM emp);
Solution
This SQL query is used to find the average sales of each designation in the 'emp' table where the average sales is greater than the overall average sales of all designations.
Here's how it works:
-
SELECT designation, avg(sales) from emp group by designation: This part of the query is selecting the 'designation' and the average 'sales' from the 'emp' table. The 'group by' clause is used to arrange identical data into groups with the help of some functions. So here, it groups the data by 'designation'. -
having avg(sales)>(SELECT avg(sales) from emp): The 'having' clause is used because you want to filter the results of your aggregation. In this case, you only want the designations where the average sales are greater than the overall average sales. The overall average sales is found by the subquery(SELECT avg(sales) from emp), which calculates the average sales of all records in the 'emp' table.
So, in summary, this query is finding the designations where the average sales are greater than the overall average sales.
Similar Questions
Which SQL function can be used to count the number of rows in a GROUP BY clause?COUNT()SUM()AVG() MAX()
Which SQL function is used to calculate the average value of a numeric column?OptionsCOUNT()AVG()MAX()SUM()
Which one of the following functions can we use to give a total of the returned rows?TOTALAVGCASTROLL UP
Select the correct answerWhich SQL function is used to find the highest value in a column?OptionsAVG()MIN()SUM()MAX()
Which SQL clause is used to filter the result set after the GROUP BY clause has been applied?
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.