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'
I think you are looking for this: