SQL: getting sum of amounts of multiple rows in single table

Question

This is my Table (invoice_payments):

invoice_payments table

I want to get rows grouped by invoice_id with sum of amount with specific condition. My SQL query:

select invoice_id,  (select sum(invoice_payments.amount)  from invoice_payments where
invoice_payments.type!='Store') as total_paid, type from invoice_payments where
invoice_payments.type!='Store' group by invoice_id

I am getting this result:

My Query Result

As you can see this is wrong. Please someone help/suggest a solution. I really appreciate any reply.

Thanks

I don't get the second select, why are you non just doing something like select invoice_id, sum(invoice_payments.amount) as total_paid, type from invoice_payments where invoice_payments.type!='Store' group by type, invoice_id ? — Fougere
– Fougere, Commented Oct 15, 2014 at 12:40

arunbabu · Accepted Answer · 2014-10-15 12:38:10Z

1

select  invoice_id,  sum(invoice_payments.amount)  
from    invoice_payments 
where   invoice_payments.type!='Store'
group   by invoice_id.

Please post the expected output if the above query satisfy the needs.

answered Oct 15, 2014 at 12:38

arunbabu

2893 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Maria Over a year ago

What about this query? How can I fix this query?

select invoice_id, sum(invoice_payments.amount) as total_paid, sum(case when type='Store' then invoice_payments.amount else 0 end ) as total_balance,  type from invoice_payments where invoice_payments.type!='Store' group by type, invoice_id

arunbabu Over a year ago

@Maria. in the above qry the total_balance always results 0.what is the need of it?

Maria Over a year ago

why will it be 0 always? I need total_paid and total_balance both

arunbabu Over a year ago

you are filtering by invoice_payments.type!='Store' , so it will fetch only the type other than store. And in the case statement you given as "when type='Store' then invoice_payments.amount" .

Maria Over a year ago

ok but I also need amount with type='store as a field, How can I acheive that?

Mureinik · Accepted Answer · 2014-10-15 12:38:46Z

1

You can use a case expression inside the sum function:

SELECT   invoice_id,
         SUM(CASE WHEN type != 'Store' THEN amount ELSE 0 END) AS total_paid
FROM     invoice_payments
GORUP BY invoice_id

answered Oct 15, 2014 at 12:38

Mureinik

316k54 gold badges401 silver badges405 bronze badges

1 Comment

Maria Over a year ago

What about this query? How can I fix this query?

select invoice_id, sum(invoice_payments.amount) as total_paid, sum(case when type='Store' then invoice_payments.amount else 0 end ) as total_balance, type from invoice_payments where invoice_payments.type!='Store' group by type, invoice_id

Collectives™ on Stack Overflow

SQL: getting sum of amounts of multiple rows in single table

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related