CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Captain16
Captain16

Reputation: 327

SQL Get the total value

I used SQL Server 2005, and I want to get the total value of the 1 invoice

Current Output:

Invoice     price    
-------------------
1234       2000    
1234       2500     
9012        3000       
3456        1000

but my select field (this is for the price) is already long and i don't know know how to add the sum on it,

   RIGHT('000000000000'+ convert(varchar,cast(A.IVC_ForeignGrossAmt as decimal(9,2))),13) as IVC_ForeignGross,

The expected output should be : 

Invoice     price   
-------------------
1234       4500        
1234       4500       
9012        3000       
3456        1000

Upvotes: 1

Views: 1648

Answers (5)

Tim Schmelter
Tim Schmelter

Reputation: 460108

You could use SUM-OVER with PARTITION BY Invoice (which is similar to a Group By):

SELECT
   Invoice, SUM(price) OVER (PARTITION BY Invoice) AS price   
FROM 
   dbo.Table

(however, not sure about the RIGHT('000000000000... part of your question)

Upvotes: 2

András Ottó
András Ottó

Reputation: 7695

You just need the SUM of the the prices based on a GROUP BY on the Invoice, and format the price as you wanted.

SELECT A.Invoice, 
RIGHT('000000000000'+ convert(varchar,cast(SUM(A.IVC_ForeignGrossAmt)
as decimal(9,2))),13) as IVC_ForeignGross 
FROM yourTable A
GROUP BY Invoice;

Upvotes: 0

Ravi
Ravi

Reputation: 31407

SELECT Invoice, SUM(price) FROM table1 GROUP BY Invoice;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Use a correlated subquery, like so:

SELECT
  Invoice,
  (SELECT SUM(t2.price)
   FROM table1 t2
   WHERE t1.invoice = t2.invoice) price           
FROM Table1 t1;

SQL Fiddle Demo

This will give you:

| INVOICE | PRICE |
-------------------
|    1234 |  4500 |
|    1234 |  4500 |
|    9012 |  3000 |
|    3456 |  1000 |

Upvotes: 2

bonCodigo
bonCodigo

Reputation: 14361

You mean the out put meant to be,

Invoice     price   
-------------------
1234       4500             
9012        3000       
3456        1000

You may try

SELECT INVOICE.ID, SUM(PRICE) FROM INVOICE
GROUP BY INVOICE.ID

Upvotes: 0

Related Questions