SQL Server sum and distinct - Add once with repeated records

Question

SQL Server sum and distinct - Add once with repeated records

Navigation

#1 by (1 votes)

0

I have this table of expenses and I need to add the Total column.

The problem is that the table takes into account the products, and when making the sum I added several times the total of a note (because in the same note there were several products). What I want is to add the total but only once per note. How could I do it?

For example: This is a sample of the table. As it is observed, the note 32097 appears twice, since in a note there are two products. The total column that appears is the Total column per note, that is why I require a distinct note and then a sum total.

That is, I need to add only once per note.

The query I have so far

select sum(Total)
from Gastos                   
where FechaGasto>='2017-01-01'and FechaGasto<='2017-12-31' and 
NombrePartida like '%activo%'

sql sql-server group-by distinct

asked by Javier Hernandez 24.04.2018 в 23:48

source

1 answer

Show all the images of a directory on my website Export document to excel with PHP

score 1 · Accepted Answer

OPTION 1: nest the query in a subtable with DISTINCT

It is assumed that you only have one price per note, and therefore, only one repetition per note will be generated.

select SUM(subtabla.Total) from 
(select DISTINCT nota, Total
where FechaGasto >='2017-01-01'and FechaGasto<='2017-12-31' and 
NombrePartida like '%activo%') as subtabla

OPTION 2: use the GROUP BY combined with MAX or 1

Group by is a function that will group the results by the field that   you suggest The power of these functions is used because   In addition, the summation fields, such as SUM, perform the aggregation   based on the GROUP BY field.

If you also have me account that MAX will give you the maximum value of one of the notes, you will only get one per line, and not the two occurrences.

select nota, MAX(Total) as Total into #agrupada
from Gastos                   
where FechaGasto>='2017-01-01'and FechaGasto<='2017-12-31' and 
NombrePartida like '%activo%' 
GROUP BY nota

Here you will have the temporary table #agrupada only with the max per note, that is one line per note.

So check the temporary table now, and you'll see the total

   select sum(Total) from #agrupada

You can do the same also by nesting the table in the from, as in the initial option.

Assuming that for each note you will always have the same "Total". you can also do (in two steps, with temporary table)

select DISTINCT Nota, Total into #agrupada
from Gastos                   
where FechaGasto>='2017-01-01'and FechaGasto<='2017-12-31' and 
NombrePartida like '%activo%' 
GROUP BY nota 

select sum(Total) from #agrupada

OPTION THAT CALCULATES VAT ACCORDING TO FIELD VAT

Regarding the comment, the following query is also generated

select sum(Total)
from ( 
  SELECT distinct nota,
  CASE
   WHEN iva = 1 THEN totalxproducto * 1.16    
   WHEN iva = 0 THEN totalxproducto
  END as Total
  from Gastos                   
  where FechaGasto>='2017-01-01'and FechaGasto<='2017-12-31
  and NombrePartida like '%activo%' 
 )

Most optimized option (you do not need to delete duplicates)

  SELECT SUM(CASE
        WHEN iva = 1 THEN totalxproducto * 1.16   
        WHEN iva = 0 THEN totalxproducto
        END)
  from Gastos                   
  where FechaGasto>='2017-01-01'and FechaGasto<='2017-12-31
  and NombrePartida like '%activo%'

Some links:

link