SQL - Calculate percentage in same select

Question

SQL - Calculate percentage in same select

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (1 votes)

0

I'm using SQL Server and I have the following query. I need to add the Percentage column. I need to calculate the percentage of Passed over Total but I do not know how to get the values of that same select to enter in the calculation.

SELECT 
  Data,
  SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed,
  SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed,
  COUNT(*) AS Total

FROM VWDADOSFPY
GROUP BY Data
ORDER BY Data DESC;

The table should look like this:

   Data         Passed        Failed        Total       Porcentagem
2018-03-15       470            5            475          98,94%
2018-03-14       485            17           502          96,61%
2018-03-13      1631            74           1705         95,65%

Can anyone help?

sql sql-server

asked by anonymous 15.03.2018 / 16:33

3 answers

2

There are a few ways.

CTE

-- código #1 v3
with totVWDADOSFPY as (
SELECT 
  Data,
  SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed,
  SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed,
  COUNT(*) AS Total
  from VWDADOSFPY
  group by Data
)
SELECT Data, Passed, Failed, Total, 
       Porcentagem= cast((Passed * 100.0 / Total) as decimal(5,2))
  from totVWDADOSFPY
  order by Data desc;

LINEAR CODE
Repeats the calculation formulas of Passed and Total to calculate the percentage in a same SELECT.

-- código #2 v2
SELECT Data,
       SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed,
       SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed,
       COUNT(*) AS Total,
       Porcentagem= cast(SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as decimal(5,2))
  from VWDADOSFPY
  group by Data
  order by Data desc;

What the query optimizer does internally is to transform code # 1 into code # 2.

15.03.2018 / 16:42

1

You can use a sub-query

SELECT Data, Passed, Failed, CAST(ROUND((Passed * 100.0) / (Passed + Failed), 2) AS DECIMAL(5,2))
FROM (
    SELECT
    Data,
    SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed,
    SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed,
    COUNT(*) AS Total
    FROM VWDADOSFPY
    GROUP BY Data
) tab
ORDER BY Data DESC;

15.03.2018 / 16:56

Get value from inside a String C # Error in vue.js filterBy

score 2 · Accepted Answer

Using sub-query, that is, a query that queries another query:

No Sql Server:

(If the fields are not decimal, you need to make the CAST in at least 1 field, and in this case always Passed and Total will be integer, CAST comes before ROUND , otherwise ROUND will always return without decimals)

SELECT Data, Passed, Failed, 
ROUND(((CAST(Valorx1 AS DECIMAL)*100)/(CAST(Valorx2 AS DECIMAL))),2)
FROM (
SELECT 
  Data,
  SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed,
  SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed
  COUNT(*) AS Total
FROM VWDADOSFPY) Alias_Tabela
GROUP BY Data
ORDER BY Data DESC;

In MySql:

SELECT Data, Passed, Failed, 
ROUND(((Passed*100)/Total),2) Porcentagem, Total
FROM (
SELECT 
  Data,
  SUM(CASE WHEN Status = 'OK' THEN 1 ELSE 0 END) as Passed,
  SUM(CASE WHEN Status = 'NOK' THEN 1 ELSE 0 END) as Failed
  COUNT(*) AS Total
FROM VWDADOSFPY) Alias_Tabela
GROUP BY Data
ORDER BY Data DESC;

Rule of 3 to find the percentage:

((Passed*100)/Total)

ROUND to round to 2 decimal places:

ROUND(valor,2)