SQL query sum unique records

149 views Asked by At

I'm trying to sum all sales of one period of time of a selling vehicle. The problem is that every product sold is one row whit amount and price and a total of the bill and the bill number.

So I have 2 options: multiply ever sold product amount whit the price and sum that. Or take the bill remove double rows and sum that. I chosen for the second option.

So now I have a [Location Code] (selling vehicle), [Bill No] and a [Total Price].

So I get:

0001    0001/00277343   10,26000000000000000000
0001    0001/00277343   10,26000000000000000000
0001    0001/00277343   10,26000000000000000000
0001    0001/00277343   10,26000000000000000000
0001    0001/00277345   10,33000000000000000000
0001    0001/00277345   10,33000000000000000000
0001    0001/00277345   10,33000000000000000000
0001    0001/00277347   24,35000000000000000000
0001    0001/00277348   30,31000000000000000000
0001    0001/00277348   30,31000000000000000000
0001    0001/00277349   2,69000000000000000000

As you see double entries, because on one bill there are more than one item. So now I just want to sum the unique price so that I get

0001 1822,50

At this moment I'm only as far as this:

select [Location Code], [Bill No_] , Price from [Item Ledger Entry] 
where [Location Code] = '0001' and [Document Date] = '01.04.2015'

I tried several but none is working. Best result gives this, but not summed

select distinct[Bill No_], [Location Code] , Price from [Item Ledger Entry] 
where [Location Code] = '0001' and [Document Date] = '01.04.2015'
2

There are 2 answers

1
Giorgi Nakeuri On BEST ANSWER

I think you are looking for this:

SELECT [Location Code], [Bill No_], SUM(Price) AS Price
FROM (SELECT  DISTINCT [Location Code], [Bill No_] , Price from [Item Ledger Entry] 
      WHERE [Location Code] = '0001' and [Document Date] = '01.04.2015') t
GROUP BY [Location Code], [Bill No_]
0
Stanislovas Kalašnikovas On
select [Location Code], [Bill No_] , SUM(Price) from [Item Ledger Entry] 
where [Location Code] = '0001' and [Document Date] = '01.04.2015'
group by [Location Code], [Bill No_]