如何在 SQL 中做百分比/总数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1183929/
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
How To Do Percent/Total in SQL?
提问by user144051
I have an typical CUSTOMER/ORDERS set of tables and I want to display the total percentageof sales a particular customer is responsible for. I can get the total number of orders in the system like so:
我有一组典型的 CUSTOMER/ORDERS 表，我想显示特定客户负责的总销售额百分比。我可以像这样获得系统中的订单总数：
SELECT COUNT(order_id) FROM orders
And I can get the the total number of orders made by the customer like so:
我可以得到客户的订单总数，如下所示：
SELECT COUNT(order_id) FROM orders WHERE cust_id = 541
How can I combine these into a single query that returns the percentage of sales for a particular customer? Thanks!
如何将这些组合成一个查询，返回特定客户的销售额百分比？谢谢！
回答by hobodave
MySQL:
MySQL：
SELECT ROUND(
100.0 * (
SUM(IF(cust_id = 541, 1, 0)) / COUNT(order_id)
), 1) AS percent_total
FROM orders;
Edit
编辑
I guess it helps if I would have noticed the postgrestag. I thought it was a MySQL question.
我想如果我注意到postgres标签会有所帮助。我认为这是一个 MySQL 问题。
PostgreSQL:
PostgreSQL：
SELECT ROUND(
100.0 * (
SUM(CASE WHEN cust_id = 541 THEN 1 ELSE 0 END) / COUNT(order_id)
), 1) AS percent_total
FROM orders;
P.S. My PostgreSQL is rusty, so if the MySQL query works on PostgreSQL I'd like to know :)
PS 我的 PostgreSQL 生锈了，所以如果 MySQL 查询适用于 PostgreSQL，我想知道 :)
Edit 2
编辑 2
I can't stress enough to be wary of the count(*) suggestion below. You generally want to avoid this with PostgreSQL.
我对下面的 count(*) 建议没有太大压力。您通常希望使用 PostgreSQL 避免这种情况。
回答by Rob Elliott
One solution is to use a nested query-
一种解决方案是使用嵌套查询 -
SELECT count(*) / (SELECT count(*) FROM orders)
FROM orders
WHERE cust_id = 541
回答by vikram
select abc.item_name, sum(amount) as total
from (select a.item_id, d.applicablefrom, a.item_name, a.final_item_status, d.rate, c.item_name as sub_item_name,
b.sub_item_qty as itemqty, (b.sub_item_qty * d.rate) as amount
from tblitem_master a,
tblitem_master c,
tblitem_bom_master b,
(select rate, applicablefrom, itemid
from tblperiodrates
where applicablefrom = (select max(applicablefrom)
from tblperiodrates
where applicablefrom<='2005-5-18')) as d
where a.item_id = b.item_id
And b.sub_item_id = c.item_id
and b.sub_item_id = d.itemid
and a.final_item_status='f') as abc
group by abc.item_name
回答by keni
select max([order].customerid) customer_id, count(orderid) customer_orders, (select count(orderid) from [order]) as total_orders,
100.0 * (count(orderid))/(select count(orderid) from [order])
from [order] inner join customer
on [order].customerid = customer.customerid
group by [order].customerid
To illustrate the flow, I have included more columns than you need to see in the final result set. Holding the count(order_id) in a temporary variable will be more efficient. I'm not used to postgres, I hope this works with minimal modification.
为了说明流程，我在最终结果集中包含了比您需要看到的更多的列。将 count(order_id) 保存在临时变量中会更有效。我不习惯 postgres，我希望这只需最少的修改即可。
