CODEWITHSUNDEEP
mysqlsql
Rajan
Rajan

Reputation: 37

Query to find the list of salesmen who had sales more than average sales for each year

I have a table (link of the table: https://docs.google.com/spreadsheets/d/11P_ElrtCXwE3NYAoP-auO7w-Et_zd6omn-v-zMdu8KA/edit?usp=sharing) from which I have to find the list of salesmen who had sales more than average sales for each year.

I have written a query below but it isn't working.

SELECT t1.salesman, t1.AVG(sale), t1.year
SUM(CASE WHEN t1.AVG(sale)>t2.AVG(sale) THEN 1 ELSE 0 END)>0
FROM Sales_temp as t1
LEFT JOIN 
(SELECT t2.year, t2.AVG(sale)
 FROM Sales_temp as t2
 Group by t2.year)
ON t1.year = t2.year
Group by t1.salesman

Any help will be highly appreciable.

Upvotes: 2

Views: 1507

Answers (3)

Sandip Chavan
Sandip Chavan

Reputation: 39

Try this : 

SELECT a.salesman, a.sale, a.year
FROM Sales_temp a
where a.sale > ( SELECT avg(b.sale) 
                 FROM Sales_temp b
                 where b.year = a.year
                group by b.year
               )

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28206

Try this:

SELECT salesman, sale, year
FROM Sales_temp
INNER JOIN 
(
    SELECT year ayear, AVG(sale) asale
    FROM Sales_temp
    GROUP BY year
) atbl
    ON year = ayear AND sale > asale
ORDER BY year, salesman;

By giving your subquery columns alias names you can do without the alias names for the tables. This simplifies things a little. I changed your LEFT JOIN to INNER JOIN, as this will restrict your output to those records that can be joined, i. e. have sale>asale.

I also added the ORDER BY clause to improve the readability of the result.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522191

For posterity's sake, if you are using MySQL version 8 or later, then we can use analytic functions here:

SELECT year, salesman, sale
FROM Sales_temp
WHERE sale > AVG(sale) OVER (PARTITION BY year);

Upvotes: 0

Related Questions