Sql как найти процент от числа

This is, I believe, a general solution, though I tested it using IBM Informix Dynamic Server 11.50.FC3. The following query:

SELECT grade,
       ROUND(100.0 * grade_sum / (SELECT COUNT(*) FROM grades), 2) AS pct_of_grades
    FROM (SELECT grade, COUNT(*) AS grade_sum
            FROM grades
            GROUP BY grade
         )
    ORDER BY grade;

gives the following output on the test data shown below the horizontal rule. The ROUND function may be DBMS-specific, but the rest (probably) is not. (Note that I changed 100 to 100.0 to ensure that the calculation occurs using non-integer — DECIMAL, NUMERIC — arithmetic; see the comments, and thanks to Thunder.)

grade  pct_of_grades
CHAR(1) DECIMAL(32,2)
A       32.26
B       16.13
C       12.90
D       12.90
E       9.68
F       16.13

CREATE TABLE grades
(
    id VARCHAR(10) NOT NULL,
    grade CHAR(1) NOT NULL CHECK (grade MATCHES '[ABCDEF]')
);

INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1002', 'B');
INSERT INTO grades VALUES('1003', 'F');
INSERT INTO grades VALUES('1004', 'C');
INSERT INTO grades VALUES('1005', 'D');
INSERT INTO grades VALUES('1006', 'A');
INSERT INTO grades VALUES('1007', 'F');
INSERT INTO grades VALUES('1008', 'C');
INSERT INTO grades VALUES('1009', 'A');
INSERT INTO grades VALUES('1010', 'E');
INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1012', 'F');
INSERT INTO grades VALUES('1013', 'D');
INSERT INTO grades VALUES('1014', 'B');
INSERT INTO grades VALUES('1015', 'E');
INSERT INTO grades VALUES('1016', 'A');
INSERT INTO grades VALUES('1017', 'F');
INSERT INTO grades VALUES('1018', 'B');
INSERT INTO grades VALUES('1019', 'C');
INSERT INTO grades VALUES('1020', 'A');
INSERT INTO grades VALUES('1021', 'A');
INSERT INTO grades VALUES('1022', 'E');
INSERT INTO grades VALUES('1023', 'D');
INSERT INTO grades VALUES('1024', 'B');
INSERT INTO grades VALUES('1025', 'A');
INSERT INTO grades VALUES('1026', 'A');
INSERT INTO grades VALUES('1027', 'D');
INSERT INTO grades VALUES('1028', 'B');
INSERT INTO grades VALUES('1029', 'A');
INSERT INTO grades VALUES('1030', 'C');
INSERT INTO grades VALUES('1031', 'F');

In this article, you will see the different ways to calculate SQL percentage between multiple columns and rows. You
will also see how to calculate SQL percentages for numeric columns, grouped by categorical columns. You will use
subqueries, the OVER clause, and the common table expressions (CTE) to find SQL percentages.

So, let’s begin without any ado.

Finding Percentage using Two Variables

There is no built-in operator that calculates percentages in SQL Server. You have to rely on basic arithmetic
operations i.e. (number1/number2 x 100) to find percentages in SQL Server.

Before finding the SQL percentages across rows and columns, let’s first see how you can find percentages using two
basic variables in SQL Server.

The script below defines three float variables @num1, @num2 and @perc. Next, the @num2 variable is divided by the
@num1 variable and the result is multiplied by 100 which is stored in the @perc variable and is printed on the
console.

Output:

Finding Percentages Between Two Columns

Finding Percentages between two columns is straightforward. You can simply use the column names and the division
operator “/” to divide values in one column by another. The result is a list of values that correspond to the result
of the division of all the values in the two columns.

Let’s see an example.

The script below, creates a table Result with two float type columns “obtained”, and “total”. The script also
inserts five dummy rows in the Result table. The SELECT query then selects all the records in the Result table. Here
is an example:

Output:

Let’s try to find percentages for each row as a result of the division between the values in the “obtained” and
“total” columns as shown below where a new column is added for percentages.

Finding Percentages via Subqueries

Finding SQL percentages between two columns is straightforward. However, the process is not as straightforward for
finding percentages across rows for different scenarios.

Let’s first discuss a very simple scenario where you have to find what is the percentage of a value in a column
among all the rows in the column.

The following script creates a table Scores with one column.

Output:

Now if you want to find what percent of the sum of total values in the “val” column does each value constitutes, you
can use subqueries.

In this regard, the outer query will multiply all the values in the “val” column by 100 which will be divided by the
result of the subquery which finds the sum of all the values in the “val” column.

Let’s first see how our subquery looks that calculate the sum of the values in the “val” column.

Output:

The following script returns the percentage of the total for each value in the “val ” column.

If you do not want to exclude any value while calculating the percentage of the total, you can do so with the WHERE
clause as shown in the script below where the value 40 is not included.

You can see from the above output that the values now have a larger percentage share of total values since the value
40 is removed.

Finally, as a side note, you can round off the percentages returned using the “round” function as shown below. The
script below rounds off the percentage values to 2 decimal places.

Output:

Let’s now see a real-world example of how you can calculate SQL percentage. You will be using the Northwind sample
database which you can download and install from this
link.

Run the following script to see the columns in the Products table.

Output:

The Products table contains columns that contain Supplier ID, Category ID, Unit Price and other information about
the products in the Northwind database.

Consider a scenario where you have to find the percentage of the products supplied by each supplier. To find such
percentage values, you need two values:

1. The total number of all the products which you can get via the COUNT function
2. The total number of products supplied for each supplier, which you can get using the GROUP BY function.

You can then multiply the 2^nd value (count of products grouped by supplier ids) by 100 and then divide
the result by the 1^st value (total count of products).

Here is how you can use subqueries to find these such SQL percentages.

Output:

The above results show that the percentage of products supplied by each supplier. For instance, you can see that the
supplier with id 1 supplied 3.89% of the total products.

USING Over Clause

The Over clause is an extremely useful Window function that calculates values over a range of values. You can use
the Over clause to calculate SQL percentages as well. With the Over clause, you can avoid the use of subqueries for
calculating percentages.

Let’s see an example. The script below finds the percentage of products supplied by every supplier. You can see that
the following script is very similar to what you saw in the previous section. However, in this case, instead of
using a subquery that returns the count of all products, we use the Over clause which returns the sum of all the
products.

The output below is similar to what you achieved using a subquery.

Output:

Let’s now see a more complex example of finding SQL percentages. Consider a scenario where for each supplier you
want to calculate the percentage of unit price for all the products. In other words, you want to find out that what
percentage of the sum of unit prices for all the products is being paid to a different supplier. You can do so with
the help of the OVER clause as follows.

In the script below, the supplier ids are displayed along with the sum of unit prices paid to all suppliers, and the
percentages of unit prices paid to all suppliers.

To calculate the sum of unit prices, the formula is simple, you can use the SUM function and pass it to the column
containing the unit prices.

To calculate the percentages for unit prices, you need to multiply the sum of unit prices for each supplier by 100
and then divide the result with the total sum of unit prices for all the suppliers. In the script below, in the
denominator, the SUM function is called twice. Since we are using the OVER operator, the first SUM function only
adds the prices for each supplier. To find the sum of unit prices paid to all suppliers, another SUM function is
called.

In the output below, you can see the supplier ids, the sum of unit prices paid to each supplier and the percentage
of unit prices for each supplier.

Output:

Using Common Table Expressions

Finally, you can also use common table expressions (CTE) to calculate percentages. Let’s first see how you can use
CTEs to find percentages between values in two columns.

The script below finds the percentages by dividing the values in the “obtained” column by the values in the “total”
column.

You can use CTE expressions to find more complex SQL percentages just as you did with the OVER clause. For example,
the script below uses the CTE to calculate the percentage of products supplied by each supplier.

The result is similar to what you achieved via the OVER clause.

Output:

Finally, you can also use the OVER clause in combination with the CTE to calculate percentages. For example, the
script below uses the CTE to find the percentage of products supplied by each supplier, along with the percentage of
unit prices paid to all the suppliers for all the products.

Conclusion

In this article, we looked at different ways to calculate SQL percentage between multiple columns and rows.

Чтобы узнать процентную долю каждого оклада от общей суммы, необходимо выполнить следующий запрос:

SELECT salary / (SELECT SUM(salary) FROM salary_statement) * 100 AS percent FROM salary_statement;

В результате получим следующее:

Если необходимо округлить получившиеся значения, то добавляем ещё функцию ROUND:

SELECT ROUND(salary / (SELECT SUM(salary) FROM salary_statement) * 100) AS percent FROM salary_statement

Получим следующий результат:

To calculate percent to total in SQL, we need to first calculate the total, and then we divide each individual value by the total to find the percentage. So this is a two-step process. There are multiple ways to accomplish this. Here we will show three different ways:

• Inline View in SELECT
• Inline View in FROM
• Using Common Table Expression (CTE)

Inline View in SELECT

The first method is to use the inline view construct in the SELECT statement. The idea here is to treat the total as a single number that we can directly use as the denominator in the division.

Let’s use an example to illustrate. Say we have the following table,

Table Total_Sales

we would type,

SELECT a1.Name, a1.Sales, a1.Sales * 1.0/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total

FROM Total_Sales a1

ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

The inline view SELECT SUM(Sales) FROM Total_Sales calculates the sum. We can then divide the individual values by this sum to obtain the percent to total for each row.

We include * 1.0 because in some databases (such as SQLite), dividing an integer by another integer results in an integer, which is not what we want. Multiplying the numerator by 1.0 forces the expression to be considered as a float by the database, and the result of the division will have the float data type, which is what we want.

Inline View in FROM

The second method is to the inline view in the FROM statement. Here, the inline view essentially becomes another table that you can query from. Note that here we do not need to specify the join condition between the two tables, as the inline view only has a single column and a single row. In this case, the SQL would become as follows:

SELECT a1.Name, a1.Sales, a1.Sales * 1.0 / a2.Total Pct_To_Total

FROM Total_Sales a1, (SELECT SUM(Sales) Total FROM Total_Sales) a2

ORDER BY a1.Sales DESC, a1.Name DESC;

Using Common Table Expression (CTE)

A third way to calculate percent to total is to use the Common Table Expression (CTE). In this case, we will first use the WITH statement to calculate the total, and then use the result in the main query to calculate the percent to total. In our example, the SQL would look like the following:

WITH Total_Sum AS (

SELECT SUM(Sales) Total FROM Total_Sales

)

List of SQL Complex Operations

Next: SQL Cumulative Percent To Total
This page was last updated on June 19, 2022.

Copyright © 2023   1keydata.com   All Rights Reserved  
  Privacy Policy     About   Contact