How to Calculate Ratios in Sql

How do I calculate ratios in SQL?

Most database systems should be able to handle this with something akin to:

Select user_id
    , Sum( Case When Result = 'Win' Then 1 Else 0 End ) / Count(*) * 1.0 As WinPercent
From Game
Group By user_id
Order By WinPercent Desc

Btw, I'm assuming you meant to say the win percentage is the total number of wins out of the total number of games. If you really did mean where result <> '', then you would need to modify the query like so:

Select user_id
    , Sum( Case When Result = 'Win' Then 1 Else 0 End ) 
        / Sum( Case When Result <> '' Then 1 Else 0 End ) * 1.0 As WinPercent
From Game
Group By user_id
Order By WinPercent Desc

Addition

It was suggested in comments that this is SQL Server specific. It is not. Everything in the above solutions is part of the SQL standard. Whether a given database system implements all of the features required is obviously another matter. So, if we are switching gears to finding a solution that would actually work on most database systems, the one feature that has the most variance in support would likely be the use of the column alias in the Order By. You can achieve the equivalent in a couple of ways but one way that would work on most database systems would be to use a derived table:

Select Z.user_id, Z.WinPercent
From    (
        Select user_id
            , Sum( Case When Result = 'Win' Then 1 Else 0 End ) 
                / Sum( Case When Result <> '' Then 1 Else 0 End ) * 1.0 As WinPercent
        From Game
        Group By user_id
        ) As Z
Order By Z.WinPercent Desc

How to calculate the ratio of records based on values of two columns distinctly with the inner join of another table?

Does this work for you?

I used a case expression inside the count() function to count the number of returned products.

The * 1.0 turns the integer division into a decimal division without explicitly casting.

Sample data

CREATE TABLE Products (
    ProductID nvarchar(5),
    Description nvarchar(50)
);

insert into Products (ProductId, Description) values
('SPO00',   'Sports product 1'),
('SPO01',   'Sports product 2'),
('SPO02',   'Sports product 3'),
('ELE00',   'Electronics product 1'),
('ELE02',   'Electronics product 2');

CREATE TABLE Orders (
    ProductID nvarchar(5),
    Bought date,
    Returned date
);

insert into Orders (ProductID, Bought, Returned) values
('ELE00', '2021-01-05', '2021-01-07'),
('SPO00', '2021-01-01', NULL),
('SPO00', '2021-01-05', '2021-01-08'),
('SPO00', '2021-01-08', NULL),
('SPO01', '2021-01-10', NULL),
('SPO01', '2021-01-15', NULL),
('SPO02', '2021-01-18', '2021-01-20');

Solution

select p.Description,
       count(case when o.Returned is not null then 1 end) as ReturnCount,
       count(1) TotalCount,
       count(case when o.Returned is not null then 1 end) * 1.0 / count(1) as ReturnRatio
from Products p
join Orders o
  on o.ProductID = p.ProductID
where p.ProductID like 'SPO%'
  and o.Bought >= '2021-01-01'
group by p.Description;

Result

Description       ReturnCount  TotalCount  ReturnRatio
----------------  -----------  ----------  --------------
Sports product 1  1            3           0.333333333333
Sports product 2  0            2           0
Sports product 3  1            1           1

Fiddle to see things in action.

SQL - Group and Aggregation to calculate ratio with respect to total

You can use window functions to compute the view percentage for each offer:

SELECT DISTINCT product, offer,
     100.0 * (COUNT(*) OVER (PARTITION BY product, offer)) / (COUNT(*) OVER (PARTITION BY product)) AS ViewPercentage
FROM offers

How to calculate the ratio of this column with 2 rows

For two rows, it's easy.
If you have a big input table, and you want to divide the first row by the second, the third row by the fourth, etc, then you need an ordering column to save yourself.

So, with a two-row table (remember, tables are never ordered), you just rely on the fact that you divide the smaller number by the bigger number.

Here goes:

WITH                                                                
-- your input ...
input(counter) AS ( -- count is reserved word, use another name ...
          SELECT 101
UNION ALL SELECT 576
)
-- cheat and just divide the smaller by the bigger
-- as "@Gordon Linoff" suggests
-- force a float division by adding a non-integer operand
-- and hard-cast it to DECIMAL(5,2)
SELECT 
  CAST(
     MIN(counter) * 1.00 / MAX(counter)
     AS DECIMAL(5,2)
  ) AS result
FROM input;
-- out  result 
-- out ----------
-- out      0.18

If, however, you have many rows, and you always need to divide the first row by the second, the third row by the fourth, that is, each odd row in the order by the next even row in the order, then you need an ordering column.

Is your problem just what you suggested, or is there more to it?

How do I calculate the ratio of two values within a SQL group?

Example

Select * 
      ,Ratio = convert(decimal(10,2),
               sum(case when [Payment_Mode]='Cash' then [Quantity]+0.0 end) over (Partition By [Item])
              /sum(case when [Payment_Mode]='Coupons' then [Quantity]  end) over (Partition By [Item])
              )
 From YourTable

Returns

Item    Payment_Mode    Quantity    Ratio
Apples  Cash            20          2.00
Apples  Coupons         10          2.00
Grapes  Cash            45          15.00
Grapes  Coupons         3           15.00
Oranges Cash            300         20.00
Oranges Coupons         15          20.00

EDIT - Another Option is with a simple Join and a conditional aggregation

Select A.*
      ,B.Ratio
 From  YourTable A
 Join  (
        Select Item
              ,Ratio = sum(case when [Payment_Mode]='Cash'  then [Quantity]+0.0 end) /NullIF(sum(case when [Payment_Mode]='Coupons' then [Quantity] end),0)
         From  YourTable
         Group By Item
       ) B  on A.Item=B.Item

SQL- calculate ratio and get max ratio with corresponding user and date details

With a recent version of most databases, you could do something like this.

This assumes, as in your data, there's one row per user per day. If you have more rows per user per day, you'll need to provide a little more detail about how to combine them or ignore some rows. You could want to SUM them. It's tough to know.

WITH cte AS (
        select a.user_name, a.date
             , a.message_sent / a.message_received AS ratio
             , ROW_NUMBER() OVER (PARTITION BY a.date ORDER BY a.message_sent / a.message_received DESC) as rn
          from messages a
     )
SELECT t.user_name, t.date, t.ratio
  FROM cte AS t
 WHERE t.rn = 1
;

Note: There's no attempt to handle ties, where more than one user has the same ratio. We could use RANK (or other methods) for that, if your database supports it.

How to calculate ratio of two values in the table for specific key

Try this:

SELECT Number1, Number2, 
       [Ratio]  / (SUM([Ratio]) OVER (PARTITION BY Number1, Number2) * 1.0)
FROM Example

Demo here

Compute ratio of group sizes using SQL

Sounds like you want something like this:

select foo, count(*),
       count(*) * 100.0 / sum(count(*)) over () as ratio
from mytable
where bar = 10 
group by foo;

This does not guarantee that the value adds up to exactly 100% when rounded. That is a much tricker problem, usually better handled at the application layer. This does produce the "correct" answer, using floating point numbers.

---

Related Topics