CODEWITHSUNDEEP
sql
Brasciole
Brasciole

Reputation: 377

SQL SUM and Average Sale by Years

I have a table where monthly sales are recorded. See image for a sample of the table.enter image description here

So this data is partial, I have months 1-12 for every year since 2000. What I would normally do is query total sales for any one year like this:

SELECT SUM(total_sales) as TotalSalesYear FROM sales_report WHERE year =  '2008'

What I want to do here is determine the highest year in sales and the average sales per year since 2000. So I need to do a SUM per year, and determine which year is the highest. Then, once I have the SUM for each year, I can come with an average of sales per year.

I'm just not sure how to query this.

Thank you.

Upvotes: 1

Views: 23410

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

Group by year in order to get a result record per year. Order it by the sales sum descending and limit your results to 1 row, so you get the year with the maximum sales sum.

Additionally we use the analytic version of AVG to get the avarage over all sums with each year record (of which we show only one at last). Remove the last line (i.e. the fetch only clause) from the query to see how it works.

select 
  year, 
  sum(total_sales) as sum_of_year, 
  avg(sum(total_sales)) over () as avg_sum
from sales_report 
group by year
order by sum(total_sales) desc
fetch first 1 row only;

SQL fiddle: http://sqlfiddle.com/#!15/fee30/5

This is standard SQL but doesn't work in every DBMS. Some DBMS are more standard compliant, others are less. Some use TOP or LIMIT or something else to get the top row only. And some don't even feature analytic functions.

Upvotes: 1

user152468
user152468

Reputation: 3242

Here is a solution for the second part of your question. This will compute the average of sum of sales over all years:

SELECT avg(sum_sales) 
FROM (
      SELECT year as year, sum(total_sales) as sum_sales     
        FROM sales 
    GROUP BY year
) AS T;

For getting the year with the highest sales, you could extend this using order by and limit as follows:

SELECT year, sum_sales   
  FROM ( 
       SELECT year as year, sum(total_sales) as sum_sales            
         FROM sales        
        GROUP BY year) AS T 
 ORDER BY sum_sales desc 
 LIMIT 1;

Upvotes: 0

Avani
Avani

Reputation: 215

  select rank()over (  order by total_sale) rnk,
 year, total_sale, avg_sale
    from (select year, 
sum(total_sales) total_sale,
 avg(total_sales) avg_sale
 from sales_report group by year)

This query provides the rank for every year. rnk=1 is the year with highest sales. By saying rnk=1, you can get the total sales and average sales for the year with highest sales. Hope this helps!

Upvotes: 0

Related Questions