Re: Tricky query

Try using the CASE construct:
CASE WHEN expr THEN expr [...] ELSE expr END
http://www.postgresql.org/idocs/index.php?functions-conditional.html

> ==========================
> Date: Tue, 30 Apr 2002 09:51:15 -0400 (EDT)
> From: Rob <rob(at)obsidian(dot)co(dot)za>
> To: PostgreSQL Server <postgres(at)obsidian(dot)co(dot)za>, <pgsql-novice(at)postgresql(dot)org>
> Subject: [NOVICE] Tricky query
> ==========================
>
> Hi all
>
> I've got a products table in my database that has the cost price
> of a
> product and another field called vatInclusive that is simply
> a boolean
> that tells me whether the cost price is vat inclusive or not.
> I also have
> a table latest_stock_count that has a field stock_count that
> contains the
> latest stock count.
>
> I want to calculate the value of my stock holdings, which is
> simply
> latest_stock_count.stock_count * products.cost_price. I have
> the
> following query which does this
>
> SELECT p.barcode, (l.stock_count * p.cost_price) AS value
> FROM latest_stock_count AS l NATURAL JOIN products AS p
> WHERE l.barcode = p.barcode;
>
> The problem is that I always want the cost_price excluding sales
> tax
> (known as VAT - which is 14%). So if vatInclusive is true, what
> I
> actually want is cost_price/1.14, not cost_price.
>
> So, to take an example, is product 12345 has a cost_price of
> 10 and a
> stock_count of 100 and the cost_price is not vatInclusive then
> the stock
> value = 1000 (100 * 10). However, if the cost price is vatInclusive,
> then
> the stock_value is 877.19 (100 * (10/1.4))
>
> Is there any way to do this in straight sql?
>
> --
> Rob
>
> He who dies with the most toys ...
>
> ... still dies
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly