Mysql returns NULL when you place SUM and there are no records

Asked

Viewed 1,051 times

0

I have the code SQL

SELECT SUM(r.quantity_domingo) AS quantity_domingo FROM reporters AS r INNER JOIN nucleos AS n ON r.id_nucleo = n.id INNER JOIN usuarios AS u ON n.id_user = u.id WHERE u.id_regiao = '1' AND MONTH(r.dia_domingo) = '11'

The problem is that in the table reports there is no record of phpmyadmin when I execute the command, it returns to me quantity_domingo as NULL. When I shoot SUM(r.quantity_domingo) AS quantity_domingo and replace by r.* he returns me "No Record" which would be right, because in PHP it keeps returning as if it had a line and I don’t want it.

php mysql
  • 1

    Wouldn’t it just be a case of you evaluating the value returned? By the way, there is something that can cause confusion there, you are using a label with the same name of the column, wouldn’t it be better AS sum_quantidade_domingo ? Not that it will change the outcome, but only by habit.

  • Regardless of solving the problem, I think hitting JOINS would help, preferably by organizing everything with LEFT JOIN. Using INNER is best only when OUTER does not solve well: http://answall.com/questions/6441

1 answer

3


This is the standard behavior of the function SUM, if there are no records it returns NULL or if it has no value either.

You can use COALESCE + SUM to solve this problem:

SELECT COALESCE(SUM(QUANTIDADE), 0) FROM RELATORIO;

Example: SQL Fiddle

  • +1, At least with COALESCE or ISNULL/IFNULL depending on DB, it can choose a return value.

  • I used the COALESCE and gave right !!! Why be the COALESCE ? (I will research also, but for what purpose ?)

  • It is a default value that you assign if the returned value is null, in which case I used zero as an example.

  • @Alissonacioli COALESCE ( aa, bb, cc, dd, ee) : If aa for NULL, bb for NULL, cc for 278, dd for NULL and ee for 883, will return 278. The coalition returns the first of the list that is not NULL, or NULL only if all are NULL. Example of use: SELECT COALESCE( email_nfe, email_principal ) AS email_contato FROM cadastro. If email_nfe is not specified ( i.e., NULL), the query returns the client’s main email, otherwise it returns the email he chose for nfe sending.

  • @Our bacco that too much!. I did not know it. If I knew I would have saved processing in many projects. Thank you!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.