CODEWITHSUNDEEP
sqlpostgresqlvariablesaggregateaggregate-functions
Dan
Dan

Reputation: 381

Simpler aggregate function in postgres select query

I have to run a few calculations in my select query to get dynamic values for something like profit received on a sold inventory item. The formula goes (price minus discounts) - (total expenses) = profit

My query works fine but I have a lot of lines like this so it's completely unreadable for anyone else. Is there a way to assign variables or something to my various aggregate functions or casts?

Here's a simpler version of my query:

select
    ...
    (sum(cast(ti.price as integer)) - sum(floor(cast(ti.discount as float)))) - sum(cast("exp".price as integer))) as profit,
    ...
from inventory as inv
    left join transaction_item as ti on ti.inventory_id = inv.id
    left join expense as exp on exp.inventory_id = inv.id
    ...
where
    ...
group by inv.id

For context:

What I'd like to do instead of that long function chain:

((price_total - total_discounts) - total_expenses) as profit

...Where I define each of those variables somewhere. Is this possible or do I need to just accept that this query will be messy?

Upvotes: 1

Views: 45

Answers (1)

The Impaler
The Impaler

Reputation: 48769

You can produce computed columns that have a name and type using CTEs (Common Table Expressions). Then, you can use them in subsequent CTEs, or in the main query.

The example below defines a CTE named inv_total that we later use in the main query. This CTE has three computed columns price_total, total_discounts, and total_expenses:

with
inv_total as ( -- first we define a CTE
  select
    ...
    sum(cast(ti.price as integer)) as price_total,
    sum(floor(cast(ti.discount as float))) as total_discount,
    sum(cast("exp".price as integer)) as total_expenses
    ...
  from inventory as inv
    left join transaction_item as ti on ti.inventory_id = inv.id
    left join expense as exp on exp.inventory_id = inv.id
    ...
  where
    ...
  group by inv.id
)
select -- now the main query uses the CTE
  price_total - total_discounts - total_expenses as profit
from inv_total;

You can chain CTEs in multiple steps (separating them by commas) to compute temp values and continue the processing. The example above has a single step.

Upvotes: 3

Related Questions