How to calculate value + sum(value) within a select

Asked

Viewed 401 times

0

Hello I plan to make a select in that direction:

select NUM_PEDIDO, VALOR, ((VALOR/ sum(VALOR)) * 100) as PORC from PEDIDO

Obviously this will return me this error: (... is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause.)

I need a select that returns the order number, the amount, and his percentage of the total.

The idea here is to calculate the percentage of the order value on the total of the orders, for example in a month I made 1,000,00 real in orders, and a given order of that month is 100,00 real, means that it amounts to 10% of the orders of that month.

But I wonder if there is any way to calculate this percentage in a select in SQL Server

sql-server select sum

  • Try with subselect: select NUM_PEDIDO, VALOR, ((VALOR/ (SELECT sum(VALOR) FROM PEDIDO)) * 100) as PORC from PEDIDO

  • this would be very bad, because sql will end up computing the sum, although it is the same, for each record of the table, that can leave very slow. I suggest computing the sum and assigning it to a variable, and then using that variable in the next select that will divide value by sum. This can be put on a precedent

1 answer

0


What you want is a percentage of a part over a whole. It’s something simple to implement using the SUM window function.

To calculate the percentage of each order item over the total order, try

-- código #1
SELECT NUM_PEDIDO, VALOR, 
       (100.0 * VALOR / sum(VALOR) over (partition by NUM_PEDIDO)) as PORC 
  from PEDIDO;

If you want to calculate percentage of each item on the sum of all orders, try

-- código #2
SELECT NUM_PEDIDO, VALOR, 
       (100.0 * VALOR / sum(VALOR) over ()) as PORC 
  from PEDIDO;

In the article Window functions (window functions) you find details about the clause OVER(), which is the key piece. If you want to deepen the study of window functions, evaluate acquire the book T-SQL Window Functions: For data analysis and Beyond.

Browser other questions tagged sql-server select sum

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