CODEWITHSUNDEEP
sql
rstreet
rstreet

Reputation: 157

Perform arithmetic in SQL

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!

Upvotes: 0

Views: 68

Answers (3)

Madhukar
Madhukar

Reputation: 1242

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]

Upvotes: 0

Purple Haze
Purple Haze

Reputation: 550

You should do it manually :

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

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

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

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

Upvotes: 1

Related Questions