Oracle SQL 查询：找出哪一年的总销售额最大
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17905427/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL Query:Find out which year total sales amount is maximum
提问by S.pal
my working table, Table name: sales
我的工作台，表名：销售
Here Is MY TABLE, [sl_no is primary key] table structure:
这是我的表，[sl_no 是主键] 表结构：
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;
我试过这个查询来查看那些[2011,2012]年的总销售额；
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....
额外添加：如果多行具有相同的值意味着....当两个年份[2011,2012]的销售额保持不变时，则....
plZ help me to Solve this problem.
请帮我解决这个问题。
采纳答案by S.pal
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));
回答by Guillem Vicens
Following select should do what you need (untested, do not have Oracle at home):
以下 select 应该可以满足您的需求（未经测试，家里没有 Oracle）：
select year, total
from (
select year, sum(sales_amount) total
from sales
group by year
)
where total = (select max(total_amount)
from (
select year, sum(sales_amount) total_amount
from sales
group by year
))
Take in account, though, that it might give you different years in each execution if two of them have exactly the same total amount. You might want to include some more conditions to avoid this.
但是，请考虑到，如果其中两个的总金额完全相同，则每次执行可能会给您不同的年份。您可能希望包含更多条件以避免这种情况。
回答by Ravi Thapliyal
This should work.
这应该有效。
with yr_agg as (
select year, sum(sales_amount) as total
from sales
group by year
)
select year, total as max_total
from yr_agg
where total = (select max(total)
from yr_agg);
回答by Gordon Linoff
I think the simplest way is to order the results and take the first row:
我认为最简单的方法是对结果进行排序并取第一行：
select year, sales_amount
from (SELECT year, SUM(sales_amount) as sales_amount
FROM sales
GROUP BY year
order by sum(sales_amount) desc
) t
where rownum = 1;
EDIT:
编辑：
If you need to display all the matching rows (which isn't mentioned in the question), I would suggest using the dense_rank()analytic function:
如果您需要显示所有匹配的行（问题中未提及），我建议使用dense_rank()分析函数：
select year, sales_amount
from (SELECT year, SUM(sales_amount) as sales_amount,
dense_rank(over order by SUM(sales_amount) desc) as seqnum
FROM sales
GROUP BY year
order by sum(sales_amount) desc
) t
where seqnum = 1;
Or, you might like the max()version instead:
或者，您可能更喜欢这个max()版本：
select year, sales_amount
from (SELECT year, SUM(sales_amount) as sales_amount,
max(sum(sales_amount)) over () as maxsa
FROM sales
GROUP BY year
order by sum(sales_amount) desc
) t
where sales_amount = maxsa;
