Say I have this table:

I want to be able to set a new column called lost based on the month and num_of_fruits columns. To set this lost column, requires a calculation. The calculation is harvested - (num_of_fruits - num_of_fruits(last_month))

I'm having trouble in the parenthesis part - getting the last month's num_of_fruits. I have this to start:

select 
    id,
    "month",
    num_of_fruits,
    harvested,
    harvested - (num_of_fruits - num_of_fruits WHERE date_trunc('month', "month" - interval '1' month)) as lost,
    selecting other columns..

It's giving me an error in the where clause.

Can you have a where clause inside a select statement? How would I take the last month's num_of_fruits and subtract it with this month's num_of_fruits - all while inside the select statement?

Any help or advice will greatly help me! Thank you so much in advance!

CodePudding user response：

If you want to check other rows in the table, you will likely want either a subquery in your SELECT or to join the table to itself.

I think you are probably trying to do:

SELECT
 harvested - (num_of_fruits - (SELECT num_of_fruits FROM mytable t2 WHERE t2.month = date_trunc('month', t1."month" - interval '1' month))) as lost
FROM mytable t1

Note that I created a whole new subquery (SELECT/FROM/WHERE) within your existing SELECT statement, instead of just adding a stray WHERE clause.

I also changed your condition so that it actually has a compares the result of DATETRUNC with something.

It's not clear to me that you actually need the DATETRUNC here (and, if you do, you might want it on both sides of the comparison), but you can use the basic idea above and fix the condition to match your needs.

An alternative (joining to self) to consider might be:

SELECT
 t1.harvested - (t1.num_of_fruits - t2.num_of_fruits)
FROM mytable t1 LEFT OUTER JOIN mytable t2
 ON t2.month = date_trunc('month', t1."month" - interval '1' month)))

If you know that you always have one row per month, so the previous row (ordered by month) is also the previous month, you could just use LAG:

SELECT
 harvested - (num_of_fruits - LAG(num_of_fruits, 1) OVER (ORDER BY month)
FROM mytable

LAG(num_of_fruits, 1) OVER (ORDER BY month) means "the num_of_fruits from the previous row in the table when the table is ordered by month".