Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How To Do Percent/Total in SQL?

Tags:

sql

postgresql

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!

like image 290
user144051 Avatar asked Jul 26 '09 06:07

user144051


People also ask

What is %s in SQL query?

%s is a placeholder used in functions like sprintf. Check the manual for other possible placeholders. $sql = sprintf($sql, "Test"); This would replace %s with the string "Test".

Is there a percent data type in SQL?

The percent ("P") format specifier multiplies a number by 100 and converts it to a string that represents a percentage. Show activity on this post. If 2 decimal places is your level of precision, then a "smallint" would handle this in the smallest space (2-bytes). You store the percent multiplied by 100.


2 Answers

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)::numeric / 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.

like image 138
hobodave Avatar answered Sep 29 '22 18:09

hobodave


One solution is to use a nested query-

SELECT count(*) / (SELECT count(*) FROM orders) FROM orders WHERE cust_id = 541 
like image 32
Rob Elliott Avatar answered Sep 29 '22 17:09

Rob Elliott