我有一个典型的CUSTOMER/ORDERS表集合,我想要显示一个特定客户负责的销售的总百分比。我可以像这样得到系统中的订单总数:
SELECT COUNT(order_id) FROM orders
我可以像这样得到客户的订单总数:
SELECT COUNT(order_id) FROM orders WHERE cust_id = 541
如何将这些组合到一个查询中,以返回特定客户的销售额百分比?谢谢!
发布于 2009-07-26 07:02:54
MySQL:
SELECT ROUND(
100.0 * (
SUM(IF(cust_id = 541, 1, 0)) / COUNT(order_id)
), 1) AS percent_total
FROM orders;
编辑
我想如果我注意到postgres的标签会有所帮助。我还以为是MySQL的问题呢。
PostgreSQL:
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;
附注:我的PostgreSQL已经生锈了,所以如果MySQL查询在PostgreSQL上工作,我想知道:)
编辑2
对于下面的count(*)建议,我再怎么强调也不为过。您通常希望在PostgreSQL中避免这种情况。
发布于 2009-07-26 07:13:37
一种解决方案是使用嵌套查询-
SELECT count(*) / (SELECT count(*) FROM orders)
FROM orders
WHERE cust_id = 541
发布于 2010-07-06 18:24:23
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
https://stackoverflow.com/questions/1183929
复制相似问题