Skip to content
Advertisement

Is there better way to subtract of sum fields without summing again in postgresql

Let’s say Point table looks like this,

point_table
- id (pk)
- amount (Integer)
- used_amount (Integer)

And I write sql like below

SELECT
     SUM(amount) as amount,
     SUM(used_amount) as used,
     SUM(amount) - SUM(used_amount) as unused,
FROM point_table

It works well. But I’m not sure it’s a best way to get subtraction of summed fields. I think it calculate unnecessary summing again.

Is there any better idea?

Thanks!

Advertisement

Answer

What about that:

SELECT
     amount, used, amount-used AS unused
FROM (
SELECT
     SUM(amount) as amount,
     SUM(used_amount) as used
FROM point_table)

or:

SELECT
     SUM(amount) as amount,
     SUM(used_amount) as used,
     SUM(amount - used_amount) as unused,
FROM point_table
3 People found this is helpful
Advertisement