How to get the sum of multiple records in a column
I have a request like this:
SELECT
TC.F_Exhibition_Name,
T.F_Exhibitor_Name,
TC.F_CreditNoteNo,
TC.F_Description,
TC.F_Price,
TC.F_Qty,
TC.F_CNqty,
TC.F_Totalamt
FROM T_CreditNote TC
LEFT JOIN T_Exhibitor T ON
T.F_Exhibitor_Name = TC.F_Exhibitor_Name
WHERE TC.F_CreditNoteNo='CNINV100002'
The result looks like this
----------------------------------------------------------------------------------------------------------------------------------------------------
F_Exhibition_Name F_Exhibitor_Name F_CreditNoteNo F_Description F_Price F_Qty F_CNqty F_Totalamt
----------------------------------------------------------------------------------------------------------------------------------------------------
WORKSPACE 2015 AEP - Associacao Empresarial De Portugal CNINV100002 item1 12 5 8 96
WORKSPACE 2015 AEP - Associacao Empresarial De Portugal CNINV100002 item2 25 12 10 250
I want to add another column [sum]
finally with the sum of the sum F_total
. In my case, I want to show sum(250+96)
= 346
.
How can i do this?
+3
source to share
2 answers
Use SUM OVER()
:
select TC.F_Exhibition_Name, T.F_Exhibitor_Name, TC.F_CreditNoteNo,
TC.F_Description, TC.F_Price,TC.F_Qty, TC.F_CNqty, TC.F_Totalamt,
sum(TC.F_Totalamt) OVER () AS SumOfTotalamt
from T_CreditNote TC
LEFT JOIN T_Exhibitor T on T.F_Exhibitor_Name=TC.F_Exhibitor_Name
where TC.F_CreditNoteNo='CNINV100002'
The windowed version SUM
will return both rows as sum F_Totalamt
.
+5
source to share
You can use an aggregate function SUM()
like
SELECT
TC.F_Exhibition_Name,
T.F_Exhibitor_Name,
TC.F_CreditNoteNo,
TC.F_Description,
TC.F_Price,
TC.F_Qty,
TC.F_CNqty,
TC.F_Totalamt,
SUM(TC.F_Totalamt) as sum_amt
FROM T_CreditNote TC
LEFT JOIN T_Exhibitor T ON
T.F_Exhibitor_Name = TC.F_Exhibitor_Name
WHERE TC.F_CreditNoteNo='CNINV100002'
GROUP BY TC.F_Exhibition_Name,
T.F_Exhibitor_Name,
TC.F_CreditNoteNo,
TC.F_Description,
TC.F_Price,
TC.F_Qty,
TC.F_CNqty,
TC.F_Totalamt
+3
source to share