How to Calculate Percentage With a SQL Statement

How to calculate percentage with a SQL statement

I have tested the following and this does work. The answer by gordyii was close but had the multiplication of 100 in the wrong place and had some missing parenthesis.

Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By Grade

SQL - Calculate percentage on count(column)

SELECT event, 
       count(event) as event_count,
       count(event) * 100.0 / (select count(*) from event_information) as event_percent
FROM event_information
group by event

Calculating percentages in SQL query

Basically, you can take your query and then join it again with all the weeks. This gives you the first three columns. You can fill in the last two using window functions:

with data as (
      SELECT WeekNo, SUM(MyAmount) As TheAmount
      FROM TryIt
      GROUP BY WeekNo
     )
select w.weekno, d.weekno as pctweek, d.theamount,
       sum(d.theamount) over (partition by w.weekno) as cumulative,
       d.theamount * 100.0 / sum(d.theamount) over (partition by w.weekno)  as pct
from data d join
     (select distinct weekno from TryIt) w
     on d.weekno <= w.weekno
order by 1, 2;

Here is a db<>fiddle.

Calculate percentage between two columns in SQL Query as another column

Try this:

SELECT availablePlaces, capacity, 
       ROUND(availablePlaces * 100.0 / capacity, 1) AS Percent
FROM mytable

You have to multiply by 100.0 instead of 100, so as to avoid integer division. Also, you have to use ROUND to round to the first decimal digit.

Demo here

Calculating a percentage from database with sql query(SQL Management studio 2012)?

Just move that Multiply by 100 inside to numerator

SELECT
name,
SUM(pass) * 100 /Sum(total) AS pass_rate
....

Calculate percentage value for each row based on calculated total in SQL Server

I think you would just add a where clause to your original query (which is the answer here)

select Customer, TradeDate,
       sum(Commission) as total_Commission,
       sum(Commission) / sum(sum(Commission)) over () as percent_commission
where TradeDate >= '2018-01-21' and
      TradeDate < '2018-01-24'
from dbo.payments p
group by Customer, TradeDate;

Notes:

• Date constants should always use YYYYMMDD or YYYY-MM-DD formats. These are ISO 8601 standards accepted by almost all databases.
• Don't use BETWEEN with dates. Here is a very good explanation by Aaron Bertrand.
• If commission is an integer, then you need a non-integer for the division. I often just add * 1.0 before the /.

sql query to calculate percentage

With one select from table )

 select state ,100 * sum( IF(gender = 'male',1,0) ) / count(*) 
    from table where state = 'nyc' 
    group by state 

How to calculate percentage with a SQL Server statement?

You can get the percentage value by dividing with the total count.

try the following:

select top 5 country, Count(country) as COUNT_COUNTRY, cast(count(country)*100.0/(select count(1) from Sales) as numeric(5,2)) as [%age]
from Sales
group by country
order by COUNT_COUNTRY desc

How to calculate a percentage on different values from same column with different criteria

One important behaviour to note is that window functions are applied after the where clause.

Because you need the window functions to be applied before any where clause (which would filter account 4000 out), they need to be used in one scope, and the where clause in another scope.

WITH
  perc AS
(
  SELECT
    *,
    credit_amount * 100.0
    /
    SUM(
      CASE WHEN account_id = 4000 THEN credit_amount END
    )
    OVER (
      PARTITION BY acct_year, accr_period
    )
      AS credit_percentage
  FROM
    your_table
)
SELECT
  *
FROM
  perc
WHERE
  account_id IN (5000,6000)

---

Related Topics