CODEWITHSUNDEEP
mysql
Alex Rey
Alex Rey

Reputation: 3

MySQL addition of null values

I´m doing a query 

SELECT ROUND((SUM(refracciones.total)+(serviciosrealizados.total)),2)
FROM refracciones, serviciosrealizados
WHERE refracciones.id_ticket = '$idticket' AND serviciosrealizados.id_ticket = '$idticket'

2 tables:

  1. refracciones
  2. serviciosrealizados

2 columns:

  1. refracciones.total
  2. serviciosrealizados.total

At the moment the query is working but just if I insert data in both table.

I will like the query show me the total of refracciones.total + serviciosrealizados.total if one of the field are empty show me the total with refracciones.total or serviciosrealizados.total

Upvotes: 0

Views: 137

Answers (1)

Joe Niland
Joe Niland

Reputation: 919

If, say, id 5 is only in serviciosrealizados, this query should do what you want:

SELECT ROUND(SUM(IFNULL(r.total, 0)) + IFNULL(s.total, 0), 2)
FROM refracciones r
  LEFT OUTER JOIN serviciosrealizados s ON r.id_ticket = s.id_ticket
WHERE r.id_ticket = 5
UNION
SELECT ROUND(SUM(IFNULL(r.total, 0)) + IFNULL(s.total, 0), 2)
FROM refracciones r
  RIGHT OUTER JOIN serviciosrealizados s ON r.id_ticket = s.id_ticket
WHERE s.id_ticket = 5;

You may have to ignore any null rows.

Here's a SQLFiddle as a demo.

You may want to review the database design if possible, as it seems awkward.

Upvotes: 1

Related Questions