如何在SQL中执行百分比/总计？ [英] How To Do Percent/Total in SQL?

问题描述

我有一个典型的客户/订单表集，我想显示特定客户负责的销售总额％。我可以这样获得系统中的订单总数：

I have an typical CUSTOMER/ORDERS set of tables and I want to display the total percentage of sales a particular customer is responsible for. I can get the total number of orders in the system like so:

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

$ b的订单中选择COUNT（order_id） $ b

如何将它们组合成一个查询，以返回特定客户的销售百分比？谢谢！

How can I combine these into a single query that returns the percentage of sales for a particular customer? Thanks!

推荐答案

MySQL：

SELECT ROUND(
  100.0 * (
      SUM(IF(cust_id = 541, 1, 0)) / COUNT(order_id)
  ), 1) AS percent_total
FROM orders;

编辑

我想如果我注意到 postgres 标签会有所帮助。我认为这是一个MySQL问题。

I guess it helps if I would have noticed the postgres tag. I thought it was a MySQL question.

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。我的PostgreSQL生锈了，所以如果MySQL查询可在PostgreSQL上运行，我想知道：）

P.S. My PostgreSQL is rusty, so if the MySQL query works on PostgreSQL I'd like to know :)

编辑2

我不能承受太大的压力，要警惕下面的count（*）建议。您通常希望在PostgreSQL中避免这种情况。

I can't stress enough to be wary of the count(*) suggestion below. You generally want to avoid this with PostgreSQL.

这篇关于如何在SQL中执行百分比/总计？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！