CODEWITHSUNDEEP
sqlms-access
nashwa
nashwa

Reputation: 221

calculated field in SQL

I need to Add a field named "calc" that is calculated as 

if D.type_id = 1
then calc = calc + D.price
and if D.type_id = 2
then calc = clac - D.price

what exactly should I add to the next SQL code to do that. 

SELECT T.type_name, D.*, I.name_inout
  FROM ((Type T
  INNER JOIN Daily D ON (T.type_id=D.type_id))
  INNER JOIN Incom I ON (I.id_inout=D.id_inout))
  WHERE D.today = DATE()
  Order by daily_id

I really don't care about the field .. I just care about the final result of "calc" as I'll pass the final value to a Report.

Upvotes: 0

Views: 79

Answers (1)

Aeroradish
Aeroradish

Reputation: 371

If I'm reading the question correctly, you need a case statement. 

SELECT 
    case D.type_id 
        when 1 then calc + D.price
        when 2 then calc - D.price
        else <<whatever your default is>>
    end as CalculatedPrice,
    T.type_name, D.*, I.name_inout
  FROM (
    (
        Type T
        INNER JOIN Daily D ON (T.type_id=D.type_id)
    )
  INNER JOIN Incom I ON (I.id_inout=D.id_inout))
  WHERE D.today = DATE()
  Order by daily_id

If you want the SUM of the calculated prices using your formula, then you need a wrapped table as follows:

select sum(dtA.CalculatedPrice)
From (
  SELECT 
    case D.type_id 
        when 1 then calc + D.price
        when 2 then calc - D.price
        else <<whatever your default is>>
    end as CalculatedPrice,
    T.type_name, D.*, I.name_inout
  FROM (
    (
        Type T
        INNER JOIN Daily D ON (T.type_id=D.type_id)
    )
  INNER JOIN Incom I ON (I.id_inout=D.id_inout))
  WHERE D.today = DATE()
 ) dtA

Upvotes: 1

Related Questions