SQL - Calculate percentage in the same select

Asked

Viewed 27,603 times

2

Based on the query below, I need to add the column Percentage, being the calculation of Passed on top of the Total but I do not know how to take the values of this same select to insert 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%

Someone can help?

sql sql-server

4 answers

3


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

In Sql Server:

(if the fields are not decimal, you need to do the CAST at least in 1 field, in which case the Passed and the Total will be whole, the CAST comes before the ROUND, if not the ROUND will always bring with no decimal)

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)
  • 1

    In T-SQL it is not possible to use an alias created in the same SELECT. See the error in the test with the code you proposed: http://sqlfiddle.com/#! 18/1257d/1

  • True @Josédiz ! I got it right... I thought of one thing and made another ! rs

  • Ok. The use of subconsultation is one of the solutions. Just add the comma before Count(), ok?

  • I thought it was the people of slutty, who comes in giving -1 to medal ! Sorry there ! Thanks for the attention !

  • Thanks for the help, I just had to adjust some things. But that was it

  • 1

    @rmneves this answer is not 100% correct. The two decimal digits will always be 00

  • @Estevaoluis explain to me how you tested ? Here is normal http://sqlfiddle.com/#! 9/43b5aa/8

  • @RBZ Voce is using Mysql. The question is about SQL Server which gives a different result

  • 1

    I had to use CAST to get the decimals.

  • Whoa, my fault, I didn’t realize !

  • Fixed. Well noted @Estevaoluis. That’s what your answer should have, or even just this comment as you did here. The intention is not a lot of answers, but good answers, well explained, answers with other forms of execution, etc... But failure happens !

Show 6 more comments

3

There are some 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 formulae for calculating Passed and Total to calculate the percentage in the 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 turn code #1 into code #2.

  • You gave a nickname with with and called from below correct !?

  • Code #1 uses CTE. In this case, it uses the nickname totVWDADOSFPY, which is then subsequently used in the second SELECT.

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;

  • Why did you use 2 sub ?

  • I was wrong to copy the query

  • @RBZ edited my answer

  • Explain what you are proposing that is different from the other answers. Yours is pretty much the same as mine, but you’re using CAST, so explain why, etc... and also your account is wrong `((Passed * 100.0) / (Passed + Failed))

  • @RBZ If you don’t use 100.0 and the cast the percentage is wrong. The result will be 98.00% instead of 98.94%

  • That’s right the part I thought was wrong. I read (Passed + Total)

  • Stephen, if you want to test your code, go to http://sqlfiddle.com/#! 18/1257d/18 which already has the data ready for use, ok?

Show 2 more comments

0

Example in SQL SERVER:

                  SELECT DISTINCT 
                   PerformanceRating                                                                                            Metric
                  ,COUNT(*) OVER(PARTITION BY PerformanceRating)                                                                Quantity
                  ,ROUND(((CAST(COUNT(*) OVER(PARTITION BY PerformanceRating) AS DECIMAL(18,2))*100)/(CAST(COUNT(*) OVER() AS DECIMAL(18,2)))),2) [Percent]
             FROM PerformanceEvaluation

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.