[Solved] Which phone has got high sales in each year?

by


In the derived table the total sales are calculated at ( year,phone ) combination. Once the total sales are calculated all the top rows( rank = 1 by sales ) should be identified for each year. By using correlated sub-queries and having clause the first row is identified from each group( year ) and displayed as final output.

SELECT year,phone, sales_per_year
  FROM
      (
      SELECT year,phone,SUM(sold_out) AS sales_per_year
        FROM sales
      GROUP BY year,phone          
      ) o
 GROUP BY year,phone
 HAVING (
           SELECT COUNT(*)
             FROM 
                   (
                      SELECT year,phone,SUM(sold_out) AS sales_per_year
                        FROM sales
                      GROUP BY year,phone                          
                    ) i  
           WHERE i.year = o.year             
             AND i.sales_per_year > o.sales_per_year 
          ) < 1          
ORDER BY year,phone,sales_per_year DESC

Check the sql fiddle link
http://sqlfiddle.com/#!9/ff096e/14

7

solved Which phone has got high sales in each year?