MYSQL query using calculated variables

Question

In the following MYSQL is there a way to use the intermediate variables received and refund to calculate the net rather than re-expressing them in the longhand query form?

SELECT reservations.id as reservationid, `gh`, `gh-br2`, `gh-loft`, `gh-br3`, `mh-br1`, `mh-br2`, `mh-all`,
              `deposit_amnt` + `pet_deposit` AS deposit, pet_fee, damage_insurance, cleaning_fee, deposit_refund_amnt, rental_amnt,
              otherFees, tax_total as tax, beginDate, endDate, rental_total, phoneNum, cellPhoneNum, email, concat(firstName,' ',lastName) AS name,
    (SELECT COALESCE(SUM(amount),0.0) FROM payments WHERE reservation_id = reservations.id AND payments.type = 'received') AS received,
    (SELECT COALESCE(SUM(amount),0.0) FROM payments WHERE reservation_id = reservations.id AND payments.type = 'refunded') AS refund,
    (SELECT COALESCE(SUM(amount),0.0) FROM payments WHERE reservation_id = reservations.id AND payments.type = 'received') -
    (SELECT COALESCE(SUM(amount),0.0) FROM payments WHERE reservation_id = reservations.id AND payments.type = 'refunded') AS net
    FROM (visitors, reservations)
    WHERE (reservations.visitorCode = visitors.id AND reservations.status = 'confirmed') $queryYear $queryCash
    ORDER BY $sortBy $_SESSION[sort_order]

There is (wrap it in a subquery) - but there's no real advantage performance-wise. AND PLEASE DON'T USE '-' in column names — Strawberry
– Strawberry, Commented Mar 25, 2014 at 22:59

Gordon Linoff · Accepted Answer · 2014-03-25 23:00:19Z

You can really fix up this query! First, learn about explicit join syntax (the use of the join and on keywords). Second, you don't need all those subqueries. You can move the logic into the from clause. Here is an alternative form for your query:

SELECT r.id as reservationid, `gh`, `gh-br2`, `gh-loft`, `gh-br3`, `mh-br1`, `mh-br2`, `mh-all`,
       (`deposit_amnt` + `pet_deposit`) AS deposit, pet_fee, damage_insurance, cleaning_fee,
       deposit_refund_amnt, rental_amnt,
       otherFees, tax_total as tax, beginDate, endDate, rental_total,
       phoneNum, cellPhoneNum, email, concat(firstName, ' ', lastName) AS name,
       p.received, p.refund, p.net
FROM visitors v join
     reservations r
     on r.visitorCode = v.id left join
     (select reservation_id,
             sum(case when p.type = 'received' then amount else 0 end) as received,
             sum(case when p.type = 'refunded' then amount else 0 end) as refunded,
             (sum(case when p.type = 'received' then amount else 0 end) -
              sum(case when p.type = 'refunded' then amount else 0 end)
             ) as net
      from payments
      group by reservation_id
     ) p
     on p.reservation_id = r.id
WHERE (r.status = 'confirmed') $queryYear $queryCash
ORDER BY $sortBy $_SESSION[sort_order]

Collectives™ on Stack Overflow

MYSQL query using calculated variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related