MySQL addition of null values

Question

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:

refracciones
serviciosrealizados

2 columns:

refracciones.total
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

WARNING: Whenever possible use prepared statements to avoid injecting arbitrary data in your queries and creating SQL injection bugs. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. — tadman
– tadman, Commented Mar 2, 2017 at 1:15

Joe Niland · Accepted Answer · 2017-03-02 02:48:02Z

1

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.

edited Mar 2, 2017 at 2:48

answered Mar 2, 2017 at 0:31

Joe Niland

9191 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Rey Over a year ago

ouch I was testing if is sum just the "servicios" and is not working if I do not insert data in refracciones :(

Alex Rey Over a year ago

is not working images eslumere.elreycalavera.com/img/sql/servicios.png eslumere.elreycalavera.com/img/sql/serviciosestructure.png eslumere.elreycalavera.com/img/sql/refracciones.png eslumere.elreycalavera.com/img/sql/refraccionesestruct.png

Alex Rey Over a year ago

interface eslumere.elreycalavera.com/img/sql/interface.png

Collectives™ on Stack Overflow

MySQL addition of null values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related