Simpler aggregate function in postgres select query

Question

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:

ti is a transaction_item table (price and discount are text columns)
inv is an inventory items table
exp is an expenses table (price is a text column)

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?

What's with all those casts? What are the types of the columns? Edit the question and provide a minimal reproducible example, i.e. the CREATE statements of the tables (paste the text, don't use images), INSERT statements for sample data (dito) and the desired result with that sample data in tabular text format. — sticky bit
– sticky bit, Commented Jan 6, 2021 at 17:53
Consider common table expressions to hold prior assignment / calculation. — Parfait
– Parfait, Commented Jan 6, 2021 at 18:10

The Impaler · Accepted Answer · 2021-01-06 18:10:14Z

3

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.

answered Jan 6, 2021 at 18:10

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

1 Comment

Dan Over a year ago

This'll do. Thanks! Using it in conjunction with COALESCE is working perfectly.

Collectives™ on Stack Overflow

Simpler aggregate function in postgres select query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related