I have a simple select query in my .NET application that is returning data from an SQL Server Database. Here is the query:

SELECT Invoice,
       Amount
FROM MyTransaction 

I need to sum the Amount column when the Invoice column has more than 1 of the same values.

For instance. If the result of the select query was:

    2Z-3451 11245.83
    2Z-3451 9.643
    2Z-3526 71.04
    2Z-3526 0.334
    2Z-3526 8710.8501
    2Z-3566 8687.273

I would like to get the sum of 2z-3451 and 2z-3526. 2z-3566 would simply have the amount of 8687.273

This is the result:

Invoice   Amount      Total
2Z-3451   11245.83  
2Z-3451   9.643       11255.743
2Z-3526   71.04
2Z-3526   0.334
2Z-3526   8710.8501   8782,2241
2Z-3566   8687.273    8687.273

CodePudding user response：

As you write in the comments that you want to also show other columns, without having affect on the group by, I assume you want to join this table to another that has more information about each invoice.
In that case you can use min or max to include these columns so you don't have to include them in the group by, this only works off course if that other table has only one row per invoice

Look at this DBFiddle for an example

select t.invoice,
       sum(t.amount) as total,
       min(t.otherfield) as otherfield,
       min(i.something) as something
from   MyTransaction t
  left join Invoice i on t.invoice = i.invoice
group by t.invoice

Another option is to not group by, but get your total by an apply

select tr.invoice,
       tr.otherfield,
       tr.amount,
       t.total
from   MyTransaction tr
  outer apply (select sum(mt.amount) total from MyTransaction mt where mt.invoice = tr.invoice) as t

See also the DBFiddle

CodePudding user response：

Use aggregate functions. SUM is the one you want here:

SELECT Invoice,
       SUM(Amount)
FROM MyTransaction
GROUP BY Invoice