Percentage calculation in SQL SERVER

Navigation
1

I have a little question that I can not solve, I searched the community here, but I did not find exactly what I wanted.

My situation resembles the following: I have N product records in my database. And in these N records, I have only 3 different codes (not the primary key) for each product, which are: 4120, 5200, and 6150. Each product has a color regardless of the code. What I need is to get the percentage of existing products with each code. Also, see if the goal (an alert type) of each code has been reached. Example ( does not have to be in html, I want to sql server myself ): 

<table>
  <thead>
    <tr>
      <td>Código</td>
      <td>Porcentagem</td>
      <td>Meta</td>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>4120</td>
      <td>35%</td>
      <td>Atingida</td>
    </tr>
    <tr>
      <td>5200</td>
      <td>45%</td>
      <td>Atingida</td>
    </tr>
    <tr>
      <td>6120</td>
      <td>20%</td>
      <td>Não atingida</td>
    </tr>
  </tbody>
</table>

I thought of doing this problem as follows: get the amount of records in a select using count. 

SELECT COUNT(ID_PRODUTO) FROM PRODUTOS

In this part I started to have doubts because I would have to get the selected code and make calculations on top of this total of records found. And then check using case when, if the percentage of each code had reached 25%. If so, the goal has been reached. If not, the goal has not been reached.

I could not solve it, I wanted to understand how I can do this, if possible using only a select.

// The required fields Table: PRODUCTS Fields: INT ID_PRODUCT, INT CODE

    
asked by anonymous 08.03.2016 / 17:55

1 answer

5

You can do this: 

DECLARE @quantidade as decimal(13, 2);
SELECT @quantidade = COUNT(ID_PRODUTO) FROM PRODUTOS

WITH CTE_PRODUTOS AS (
    SELECT 
        CODIGO,
        (COUNT(ID_PRODUTO) * 100) / @quantidade AS PORCENTAGEM
    FROM PRODUTOS
    GROUP BY CODIGO
)

SELECT 
    CODIGO, 
    PORCENTAGEM, 
    CAST(CASE WHEN PORCENTAGEM >= 25 THEN 1 ELSE 0 END AS BIT) AS META 
FROM CTE_PRODUTOS
    
08.03.2016 / 18:27
Change betas graphs of a regression How to store in a variable the number of rows found from a MySQL query