Firebird: sum of the multiplication of two fields within a select statement

case, firebird, multiplication, sql

Solution

I think that what is wrong on your query is that your CASE statement is missing the END. Try this:

SELECT  
    D.id,
    D.name,
    SUM(CASE WHEN D.sale_unity = 'K' 
        THEN (D.cost_price * CI.quantity_k)
        ELSE (D.cost_price * CI.quantity_u) END ) AS total_cost,
    SUM(CASE WHEN D.sale_unity = 'K' 
        THEN (D.sale_price * CI.quantity_k)
        ELSE (D.sale_price * CI.quantity_u) END ) AS net
FROM DETAIL D
INNER JOIN VOUCHER AS V ON V.id = D.voucher_id
WHERE V.client_id = 97 AND V.date BETWEEN '06/01/2012' AND '07/01/2012'
GROUP BY D.id, D.name

Problem

Basically what I need is to get the sum of the multiplication of two fields. For example: `SUM ((field_1 or field_2) * field_3)` But `field_1` or `field_2` are selected based on the value of a fourth field, `field_4` My table: ``` field_1: Float field_2: Float field_3: Float field_4: Char ``` In short, what i need is something like this: ``` SELECT ... IF field_4 = 'K' THEN SUM( field_1 * field_3 ) ELSE SUM( field_2 * field_3 ) ``` I tried with IF and CASE statements, but i can't make it work. Here my real query: ``` SELECT D.id, D.name, SUM(CASE WHEN D.sale_unity = 'K' THEN (D.cost_price * CI.quantity_k) ELSE (D.cost_price * CI.quantity_u) ) AS total_cost, SUM(CASE WHEN D.sale_unity = 'K' THEN (D.sale_price * CI.quantity_k) ELSE (D.sale_price * CI.quantity_u) ) AS net FROM DETAIL D INNER JOIN VOUCHER AS V ON V.id = D.voucher_id WHERE V.client_id = 97 AND V.date BETWEEN '06/01/2012' AND '07/01/2012' GROUP BY D.id, D.name ```

Original source