Oracle SQL Query:Find out which year total sales amount is maximum

sql oracle sum max

16,985

Here is my Query where multiple row can select

SELECT year,MAX(total_sale) as max_total
FROM 
    (SELECT year,SUM(sales_amount) AS total_sale FROM sales GROUP BY year)
GROUP BY 
    year HAVING MAX(total_sale) = 
           (SELECT MAX(total_sale) FROM (SELECT SUM(sales_amount) AS total_sale FROM sales GROUP BY year));

16,985

Author by

S.pal

i,m student.

Updated on June 04, 2022

Comments

S.pal almost 2 years

my working table, Table name: sales

Here Is MY TABLE, [sl_no is primary key] table structure:

CREATE TABLE  SALES 
  ( SL_NO NUMBER PRIMARY KEY, REGION VARCHAR2(10) NOT NULL, 
    MONTH VARCHAR2(20) NOT NULL, YEAR NUMBER NOT NULL, 
    SALES_AMOUNT NUMBER NOT NULL )

and here is table data:

SQL> select * from sales;

     SL_NO REGION     MONTH                      YEAR SALES_AMOUNT
---------- ---------- -------------------- ---------- ------------
         1 east       december                   2011       750000
         2 east       august                     2011       800000
         3 west       january                    2012       640000
         5 east       march                      2012      1200000
         6 west       february                   2011       580000
         4 west       april                      2011       555000

6 rows selected.

I have tried this query to view total sales amount of those[2011,2012] year;

 SELECT year, SUM(sales_amount) FROM sales GROUP BY year;

      YEAR SUM(SALES_AMOUNT)
---------- -----------------
      2011           2685000
      2012           1840000

MY GOAL:> I want to find out the year of maximum sales amount. I tried this,and work perfectly...but when i want to display that year also, it gives an Error.

SQL> select  max(sum(sales_amount)) from sales group by year;

MAX(SUM(SALES_AMOUNT))
----------------------
               2685000

SQL> select year, max(sum(sales_amount)) from sales group by year;
select year, max(sum(sales_amount)) from sales group by year
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Extra addition: if multiple rows have same value means....when sales amount of both year[2011,2012] remain same, Then....

plZ help me to Solve this problem.

S.pal almost 11 years

i have SQL Plus: Release 10.2.0.1.0. Your suggestion is not working.... it gives an Error: " ERROR at line 1: ORA-00936: missing expression "
S.pal almost 11 years

is it possible,if sales amount of both year[2011,2012] remain same, then is it work ??
Guillem Vicens almost 11 years

The problem was I forgot to rename the SUMcolumn to total in the first subselect. I will correct it so anybody checking this question can decide whether to use my answer or Ravi's one. Both are semantically equivalent
a_horse_with_no_name almost 11 years

@pal: the version of SQL*Plus (the "client") is irrelevant, if at all you need to supply the version of the Oracle server
S.pal almost 11 years

sorry, it gives an Error. ERROR at line 1: ORA-00935: group function is nested too deeply
S.pal almost 11 years

I have tried this,but i think this is wrong way.....through it we can display 1st row which is large one. But if sales amount of both year[2011,2012] remain same, then only one is displayed. We need to display all rows.
S.pal almost 11 years

i used this query which properly works and it also simplest way. select * from (select year, sum(sales_amount) from sales group by year order by sum(sales_amount) desc) where rownum = 1;
S.pal almost 11 years

SORRY give an Error. select year, sales_amount from (SELECT year, SUM(sales_amount), max(sum(sales_amount)) over () as maxsa ...., ERROR at line 1: ORA-00904: "SALES_AMOUNT": invalid identifier
Gordon Linoff almost 11 years

@pal . . . I had accidentally deleted the column alias.
Guillem Vicens almost 11 years

@pal, two parentheses were missing. It is corrected and tested now (today I have access to Oracle again)