Perform arithmetic in SQL

Question

could you please help me with this? I have the following query which returns country name, number of records for each country, and total number of countries . How can I get it to also return a column for % of each country with respect to total number. Ideal output would be something like.

USA, 25, 100, 25% ... UK, 28, 100, 28% ... etc...

SELECT Country, COUNT(Country) AS number, (SELECT COUNT(Country) 
FROM[Customers]) AS total FROM [Customers] 
GROUP BY Country ORDER BY number DESC

I have tried number/total AS percent but it didn't work. Obviously I am doing something wrong.

I want to be able to filter countries that are above certain percentage say 20%.

Thanks!

Possible duplicate of https://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement — MatSnow, Aug 15 '17 at 13:28

score 1 · Accepted Answer · answered Aug 15 '17 at 13:18

1

Use a derived table, which is a subquery with an alias.

select number/total AS percent 
from (
your query goes here
) derivedTable

answered Aug 15 '17 at 13:18

Dan Bracuk

20,699
4
26
43

thanks a lot! I just had to multiply "number" by 100 to get the percent like 5, 12, etc. – rstreet Aug 15 '17 at 14:06

score 0 · Answer 2 · edited Aug 15 '17 at 13:19

0

You should do it manually :

SELECT Country, COUNT(Country)*100/ (SELECT COUNT(Country) FROM[Customers]) AS total FROM [Customers] where total >20

edited Aug 15 '17 at 13:19

Kamil Gosciminski

16,547
8
49
72

answered Aug 15 '17 at 13:19

Purple Haze

530
7
22

Madhukar · Answer 3 · 2017-08-15T13:39:49.440

0

Below SQL would help you.

SELECT Country, COUNT(Country) AS number, (SELECT COUNT(Country) FROM[Customers]) AS total, ROUND(COUNT(Country) * 100.0 / (SELECT COUNT(Country) FROM[Customers]), 1) FROM [Customers]

edited Aug 15 '17 at 13:39

answered Aug 15 '17 at 13:20

Madhukar

1,194
1
13
29

Perform arithmetic in SQL

3 Answers3