CODEWITHSUNDEEP
mysqlsql
RostD
RostD

Reputation: 115

How i can use result of subquery in this query?

Example, I have SQL - query:

SELECT
    (SELECT value->>"$[0].value" 
        FROM leads_fields 
        WHERE leads_fields.field_id = 244991 AND leads_fields.lead_id = leads.external_id)
        as 'coefRL',
    (SELECT value->>"$[0].value" 
        FROM leads_fields 
        WHERE leads_fields.field_id = 244943 AND leads_fields.lead_id = leads.external_id)
        as 'coefWF'

FROM leads

And in order to get the difference of these values, I need to duplicate these requests with the 3rd subquery like this?

(
    (SELECT value->>"$[0].value" 
     FROM leads_fields 
     WHERE leads_fields.field_id = 244991 AND leads_fields.lead_id = leads.external_id)
        -
     (SELECT value->>"$[0].value" 
      FROM leads_fields 
      WHERE leads_fields.field_id = 244943 AND leads_fields.lead_id = leads.external_id)
) as 'difference'

Or there are ways more practical, for example (coefRL - coefWF) ?

Upvotes: 0

Views: 39

Answers (1)

MagdielAybar
MagdielAybar

Reputation: 187

This is a way to do it without a 3rd subquery...

SELECT coefRL,
  coefWF,
  coefRL-coefWF difference
FROM
  (SELECT
    (SELECT value->>"$[0].value"
    FROM leads_fields
    WHERE leads_fields.field_id = 244991
    AND leads_fields.lead_id    = leads.external_id
    ) AS 'coefRL',
    (SELECT value->>"$[0].value"
    FROM leads_fields
    WHERE leads_fields.field_id = 244943
    AND leads_fields.lead_id    = leads.external_id
    ) AS 'coefWF'
  FROM leads
  )

Upvotes: 1

Related Questions