Learn From - Interactive SQL



SQL Find Percentage


Usually there are more than one way of achieving the desired Resultset using SQL. Here we show you two ways of achieveing the same result.

SQL using subquery and the same result achieved using CROSS JOIN (Cartesian Product).

Below is shown the "Customers" table:

CustomerID CustomerName EmailID Address City PostalCode Country
C001 Fredrick Johnson FJohnson@yahoo.com Berkeley Gardens 12 Brewery London WX1 6LT UK
C002 Mary Davidson Mary@gmail.com 35 King George Madrid 28023 Spain
C003 George Clooney George@hotmail.com Obere Str. 57 Berlin 12209 Germany
C004 David Louis David@mail.com 2743 Bering St. NewJersy 07019 USA
C005 Johnny Anderson Johnny@gmail.com 55 Grizzly Peak Rd. NewJersy 07019 USA
C006 Paul Henriot PaulHenriot@yahoo.com 59 rue de l'Abbaye Paris 51100 France
C007 Henry King Henry@mail.com 120 Hanover Sq. London WA1 1DP UK
C008 Robert John Robert@gmail.com 87 Polk St. Suite 5 San Francisco 94117 USA

SQL using SUBQUERY


To retrieve the count of customers in each country and also the percentage of customers country wise.
Select Cust.Country, COUNT(Cust.City) CountOfUsers,
ROUND(CONVERT(decimal, COUNT(Cust.Country))/ (Select count(Country) TotalCount From Customers), 4) * 100 Percentage
From Customers as Cust
GROUP BY Cust.Country
ORDER BY Percentage DESC;

The above SQL Query will result in the following output.

Country CountOfUsers Percentage
USA 3 37.50000000000
UK 2 25.00000000000
France 1 12.50000000000
Germany 1 12.50000000000
Spain 1 12.50000000000

Note: The COUNT(Cust.Country) subquery is converted to decimal and later divided by the total count of Customers, which is an integer. Since we divide a decimal by an integer, we get the values in decimal. This is then multiplied by 100 to get the percentage.

If you want to avoid so many decimal places being shown in the Percentage column, we can modify the above query as shown below to get the formatted output.

Text Editor below has the SQL Query, click the "View Result" button, to see the result.

select tbl.Country, tbl.CountOfUsers, CONVERT(Varchar(10),
CONVERT(tinyint, tbl.Percentage)) + '%' Percentage
From
(
    Select Cust.Country, COUNT(Cust.City) CountOfUsers,
    ROUND(CONVERT(decimal, COUNT(Cust.Country))/ (Select count(Country) TotalCount From Customers), 4) * 100 Percentage
    from Customers as Cust
    GROUP BY Cust.Country
) as tbl
ORDER BY tbl.Percentage DESC;

Note: The use of "CONVERT" Function on the "Percentage" column changes it to datatype tinyint. Hence in this mannner we avoid the zeroes. We can then appended with "%" symbol.

Above Query Result achieved using CROSS JOIN (Cartesian Product)


Cartesian product is joining a table in a way that all the records of the first table is joined with each and every record of the other table.

You can see the cartesian product of the "Customers" table itself at work.

select tbl2.Country, tbl2.CountOfUsers, CONVERT(Varchar(10),
CONVERT(tinyint, tbl2.Percentage)) + '%' Percentage
From
(Select Cust.Country, COUNT(Cust.City) CountOfUsers,
ROUND(CONVERT(decimal, COUNT(Cust.Country))/ tbl1.TotalCount, 4) * 100 Percentage
From Customers Cust
CROSS JOIN (Select count(Country) TotalCount From Customers) as tbl1
GROUP BY Cust.Country, tbl1.TotalCount ) as tbl2
Order By tbl2.Percentage DESC;

Note: The highligted code shows the CROSS JOIN to get the cartesian product. Later the cartesian product is used as a subquery for formatting the output of the "Percentage" column.

SQL in Text Editor: