blocks|key|3312851|text|MySQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3312852|SELECT+ROUND(
++100.0+*+(
++++++SUM(IF(cust_id+=+541,+1,+0))+/+COUNT(order_id)
++),+1)+AS+percent_total
FROM+orders;|code-block|syntax|javascript|3312853|编辑|offset|length|style|BOLD|3312854|我想如果我注意到postgres的标签会有所帮助。我还以为是MySQL的问题呢。|3312855|PostgreSQL：|3312856|SELECT+ROUND(
++100.0+*+(
++++++SUM(CASE+WHEN+cust_id+=+541+THEN+1+ELSE+0+END)::numeric+/+COUNT(order_id)
++),+1)+AS+percent_total
FROM+orders;|3312857|附注:我的PostgreSQL已经生锈了，所以如果MySQL查询在PostgreSQL上工作，我想知道:)|3312858|编辑2|3312859|对于下面的count(*)建议，我再怎么强调也不为过。您通常希望在PostgreSQL中避免这种情况。|3312860|entityMap^0|0|0|0|2|0|8|9|0|0|0|0|0|3|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@$I|13|J|14|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|15|8|@$I|16|J|17|K|L]]|9|@]|A|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1A|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1B|8|@$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|Z|$]]

MySQL:
<pre><code>SELECT ROUND(
 100.0 * (
 SUM(IF(cust_id = 541, 1, 0)) / COUNT(order_id)
 ), 1) AS percent_total
FROM orders;
</code></pre>
Edit
I guess it helps if I would have noticed the postgres tag. I thought it was a MySQL question.
PostgreSQL:
<pre><code>SELECT ROUND(
 100.0 * (
 SUM(CASE WHEN cust_id = 541 THEN 1 ELSE 0 END)::numeric / COUNT(order_id)
 ), 1) AS percent_total
FROM orders;
</code></pre>
P.S. My PostgreSQL is rusty, so if the MySQL query works on PostgreSQL I'd like to know :)
Edit 2
I can't stress enough to be wary of the count(*) suggestion below. You generally want to avoid this with PostgreSQL.

blocks|key|333565|text|一种解决方案是使用嵌套查询-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|333566|SELECT+count(*)+/+(SELECT+count(*)+FROM+orders)
FROM+orders
WHERE+cust_id+=+541|code-block|syntax|javascript|333567|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

One solution is to use a nested query-

<pre><code>SELECT count(*) / (SELECT count(*) FROM orders)
FROM orders
WHERE cust_id = 541
</code></pre>

blocks|key|3100001|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3100002|为了说明这个流程，我包含的列比您在最终结果集中看到的列还要多。将计数(Order_id)保存在临时变量中会更有效率。我不习惯postgres，我希望它能在最小的修改下工作。|unstyled|3100003|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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
</code></pre>

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.

blocks|key|336150|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|336151|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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&lt;='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
</code></pre>

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:

<pre><code>SELECT COUNT(order_id) FROM orders
</code></pre>

And I can get the the total number of orders made by the customer like so:

<pre><code>SELECT COUNT(order_id) FROM orders WHERE cust_id = 541
</code></pre>

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

How To Do Percent/Total in SQL?

PostgreSQL

MySQL

我有一个典型的CUSTOMER/ORDERS表集合，我想要显示一个特定客户负责的销售的总百分比。我可以像这样得到系统中的订单总数：SELECT COUNT(order_id) FROM orders我可以像这样得到客户的订单总数：SELECT COUNT(order_id) FROM orders WHERE cust...

问如何在SQL中实现Percent/Total？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中实现Percent/Total？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中实现Percent/Total？
EN