How To Do Percent/Total in SQL?

Question

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

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

Answer 1

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 postgres tag. I thought it was a MySQL question.

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 :)

Edit 2

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

Answer 2

One solution is to use a nested query-

SELECT count(*) / (SELECT count(*) FROM orders)
FROM orders
WHERE cust_id = 541

Answer 3

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.

Answer 4

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