Как найти проценты 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');

There are different ways to calculate percentage in SQL like:

• Using Ratio_to_report() function or the Percent_Rank function
• Using OVER() clause
• Using subquery
• Using CTE

Calculating percentage in SQL

Let us consider the ‘inventory’ table as below. We can calculate the percentage of each item in the inventory.

CREATE TABLE inventory(
       item VARCHAR(50),
       avail_stock INT
);

INSERT INTO inventory
SELECT 'laptop', 20 UNION ALL
SELECT 'keyboard', 40 UNION ALL
SELECT 'mouse', 70 UNION ALL
SELECT 'speaker', 20 UNION ALL
SELECT 'Monitor', 50;

Snowflake SQL

In Snowflake, you can use the Ratio_to_report() function or the Percent_Rank function, depending on your use case.

RATIO_TO_REPORT()
select
    item,
    stock,
    ratio_to_report(stock) over () as overall_stock_pct
from inventory

Keep Tabs on your “Calculated(%)” SQL Using Datameer’s Graphical Interface

Datameer is a collaborative, multi-persona data transformation platform that integrates with Snowflake.

With Datameer on Snowflake, you can use the SQL functions you’re familiar with to calculate your percentage and visually track all your calculated percentage queries with our low-code GUI interface.

To reap the benefits of these easy drag-and-drop modeling and self-documenting features, kickstart your Snowflake instance and connect your Datameer account.

SQL SERVER

-- Using OVER Clause
SELECT item Item, avail_stock * 100.0/ SUM(avail_stock) OVER() 'Percentage(%)'
FROM inventory

-- Using CROSS APPLY
SELECT item Item, avail_stock * 100.0/ sub.sum_avail_stock 'Percentage(%)'
FROM inventory
CROSS APPLY (SELECT SUM(avail_stock) sum_avail_stock FROM inventory) sub

-- Using subquery in SELECT statement
SELECT item Item, avail_stock * 100.0/ (SELECT SUM(avail_stock) FROM inventory) 'Percentage(%)'
FROM inventory

-- Using CTE
;WITH total AS
(
      SELECT SUM(avail_stock) AS total
    FROM inventory
)
SELECT item Item,
    avail_stock * 100 / total.total AS 'Percentage(%)'
FROM inventory
CROSS JOIN total;

-- Output of all above queries
Item        Percentage(%)
----------------------------
laptop      10.000000000000
keyboard    20.000000000000
mouse       35.000000000000
speaker     10.000000000000
Monitor     25.000000000000

MySQL

-- Using OVER Clause
SELECT item Item, avail_stock * 100.0/ SUM(avail_stock) OVER() 'Percentage(%)'
FROM inventory;

-- Using Subquery in SELECT statement
SELECT item Item, avail_stock * 100.0/ (SELECT SUM(avail_stock) FROM inventory) 'Percentage(%)'
FROM inventory;

-- Using CROSS JOIN
SELECT item Item, avail_stock * 100/ sub.sum_avail_stock 'Percentage(%)'
FROM inventory
CROSS JOIN (SELECT SUM(avail_stock) sum_avail_stock FROM inventory) sub;

-- Output of all above queries
Item       Percentage(%)
----------------------------
laptop     10.000000000000
keyboard   20.000000000000
mouse      35.000000000000
speaker    10.000000000000
Monitor    25.000000000000

Up Next:

Read In SQL, how to limit the number of rows after ordering it in Oracle DB?

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

Вы уж извините, но то что Вы написали в запросе … это даже приблизительно не то что Вы хотели получить на словах.
Да и на словах как то все поплыло “Вывести всех клиентов, у которых есть 5 пар клиентов” – кто на ком стоял?

Если в 2 словах по запросу, у Вас 2 LEFT JOIN, оба не нужны.
У Вас нет вообще связки между двумя экземплярами orders, да и не надо там 2 раза.
Ну в целом …. работает долго и непонятно, будет данных больше, будет работать днями …

P.S. после долгих уточнений подходящий ответ такой, для MySQL 8+

select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (
        select 
        arur.af_id,
        arur.r_w_uid,
        lag(arur.r_w_uid,1,-1) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
        ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag
        
        from arur, u_af 
        where u_af.id = arur.af_id ) t1  left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(distinct r_w_uid) >  5

Для более младших версий

select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (SELECT arur.af_id,
       arur.r_w_uid, 
       @row_num :=  CASE WHEN @row_num_val = af_id                THEN @row_num+1
                         WHEN (@row_num_val := af_id) IS NOT NULL THEN 1
                    END   pair_flag,
       @lag_r_w_uid :=  CASE WHEN (@row_num_val = af_id) and @row_num mod 2 = 1 THEN r_w_uid
                             else @lag_r_w_uid
                        END r_w_uid_1     

FROM arur, u_af, (SELECT @row_num := null, @row_num_val := null, @lag_r_w_uid := null) AS x
where u_af.id = arur.af_id 
ORDER BY af_id, r_w_uid        ) t1  left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag mod 2 = 0
group by af_id
having count(distinct r_w_uid) >  5