oracle - 对子查询求和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/783070/
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 - sum on a subquery?
提问by
Wonder if anyone can help me understand how to sum up the column of single column, ie a list of costs into one total cost.
不知道有没有人能帮我理解一下如何将单列的一列求和，即将成本列表合并为一个总成本。
I have been looking into this, and I think I'm on the right lines in undertsanding i need to sum the query, treat it as a subquery. However I'm not having much luck - do I need to give the subquery an alias, or is it a straight case of wrapping the query in a sum?
我一直在研究这个，我认为我在undertsanding的正确行我需要总结查询，将其视为子查询。但是，我运气不佳 - 我是否需要为子查询提供别名，还是将查询包装在总和中的直接情况？
Here is the working query I want to sum up, all my attempts at sum left out for clarity!
这是我想总结的工作查询，为了清楚起见，我对 sum 的所有尝试都被省略了！
SELECT TICKET_TYPE.PRICE AS TOTALSALES, RESERVATION.RESERVATION_ID,
CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE
FROM RESERVATION, TICKET, TICKET_TYPE, CINEMA, PERFORMANCE
WHERE TICKET_TYPE.TICKET_TYPE_ID = TICKET.TICKET_TYPE_ID
AND TICKET.RESERVATION_ID = RESERVATION.RESERVATION_ID
AND RESERVATION.PERFORMANCE_ID = PERFORMANCE.PERFORMANCE_ID
AND CINEMA.LOCATION = 'sometown'
AND PERFORMANCE.PERFORMANCE_DATE = to_date('01/03/2009','DD/MM/yyyy');
some of the data...
一些数据...
TOTALSALES RESERVATION_ID LOCATION PERFORMANCE_DATE
2.8 1 sometown 01-MAR-09
3.5 2 sometown 01-MAR-09
2.8 3 sometown 01-MAR-09
2.8 3 sometown 01-MAR-09
2.8 3 sometown 01-MAR-09
2 4 sometown 01-MAR-09
2.8 5 sometown 01-MAR-09
Thanks !
谢谢 ！
采纳答案by northpole
Try:
尝试：
You need to include a group by if you want the totals per ID.
如果您想要每个 ID 的总数，您需要包含一个组。
SELECT SUM(TICKET_TYPE.PRICE) AS TOTALSALES
, RESERVATION.RESERVATION_ID
, CINEMA.LOCATION
, PERFORMANCE.PERFORMANCE_DATE
FROM RESERVATION
, TICKET
, TICKET_TYPE
, CINEMA
, PERFORMANCE
WHERE TICKET_TYPE.TICKET_TYPE_ID = TICKET.TICKET_TYPE_ID
AND TICKET.RESERVATION_ID = RESERVATION.RESERVATION_ID
AND RESERVATION.PERFORMANCE_ID = PERFORMANCE.PERFORMANCE_ID
AND CINEMA.LOCATION = 'sometown'
AND PERFORMANCE.PERFORMANCE_DATE = to_date('01/03/2009','DD/MM/yyyy');
GROUP BY RESERVATION.RESERVATION_ID
**** pretty much the same answer as above, I need to get better at refreshing before posting****
**** 与上面的答案几乎相同，我需要在发帖前更好地刷新****
回答by Shea
You can sum a single column with
您可以将单个列与
select sum(mycolumn) from mytable
从 mytable 中选择 sum(mycolumn)
When you mix aggregators (e.g., sum(), count()) in the select list with fields then you change the meaning of the query. You have to include a GROUP BY clause and the clause must contain every non-aggregate part of the select list.
当您将选择列表中的聚合器（例如 sum()、count()）与字段混合使用时，您会更改查询的含义。您必须包含一个 GROUP BY 子句，并且该子句必须包含选择列表的每个非聚合部分。
You could do the sum, by itself, in a nested subquery then include that output in the outer select...
您可以在嵌套子查询中单独进行求和，然后将该输出包含在外部选择中...
回答by Denis Troller
What you first need to know is what the "non summed part" of the query should be.
您首先需要知道的是查询的“非求和部分”应该是什么。
You want to calculate a SUM of X by Y (Sum of SALES by MONTH for example). Y can be any number of fields and the resulting data set will contain one record for each distinct combination of the Y fields.
您想计算 X 乘 Y 的总和（例如，按月计算的销售额总和）。Y 可以是任意数量的字段，结果数据集将包含 Y 字段的每个不同组合的一条记录。
Once you know that we can help you write your query.
一旦您知道我们可以帮助您编写查询。
