CODEWITHSUNDEEP
mysqlsqlcodeigniter
Rene
Rene

Reputation: 13567

return 0 when empty result sql

Can i return 0 as an result when there is an empty resultset

SELECT 
  SUM(`Uren`),
  SUM(`Minuten`) 
FROM
  `Uren` 
  RIGHT JOIN `Project` 
    ON `Project`.`idProject` = `Uren`.`idProject` 
  RIGHT JOIN `Uursoort` 
    ON `Uursoort`.`idUursoort` = `Uren`.`idUursoort` 
WHERE `idKlant` = 5 
  AND `Uren`.`Datum` > '2014-04-02' 
GROUP BY `Uren`.`idProject`

Prefered output (instead of empty resultset):

sum('Uren')     sum('Minuten')
0               0

when i do it like this:

SELECT 
  SUM(`Uren`),
  SUM(`Minuten`) 
FROM
  `Uren` 
  RIGHT JOIN `Project` 
    ON `Project`.`idProject` = `Uren`.`idProject` 
  RIGHT JOIN `Uursoort` 
    ON `Uursoort`.`idUursoort` = `Uren`.`idUursoort` 
WHERE `idKlant` = 5 
  AND `Uren`.`Datum` > '2014-04-02' 
GROUP BY `Uren`.`idProject

i get right output because there is data. But i need to get 0 as an return. i also tried this in the model:

    function rapport_detail_uren($idKlant,$start,$eind){
            $this->db->select_sum('Uren');
            $this->db->select_sum('Minuten');
            $this->db->from('Uren');
            $this->db->join('Uursoort','Uursoort.idUursoort=Uren.idUursoort','right');
            $this->db->join('Project','Project.idProject=Uren.idProject','right');
            if ($idKlant > 0){
            $this->db->where('idKlant',$idKlant);}  
if ($start != NULL){
        $this->db->where('Uren.Datum >=', $start);
        }

            $this->db->group_by('Uren.idProject');

            $query = $this->db->get();
            if($query->num_rows()>0){
                return $query->result();
            }
            else{
                return 0;
            }
    }

the last line return 0. i thought maybe this will return the value's as 0 but that doesnt work

Upvotes: 0

Views: 3249

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

What you ask is not easily done. (Or at least it gets awkward.) Your select statement returns zero, one or more rows. In case it returns none, you want a row generated. So you must execute your statement twice; once for the normal rows and once to determine wether you need to create an addidtional row:

select col1, col2 -- <= your statement 
from some tables
where some criteria
UNION ALL -- Add ...
select 0, 0 -- ... a record containing zeros, but ...
where not exists -- ... due to this clause only when your query returns no records.
(
  select col1, col2  -- <= your statement again
  from some tables
  where some criteria
);

Just copy and paste your own statement (which I still consider dubious btw).

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

This should be working: 

SELECT 
  SUM( `Uren` ) AS SUM_Uren,
  SUM( `Minuten` ) AS SUM_Minuten
FROM
(
  SELECT 
    `Uren`.`idProject
    IF( count( `Uren` ) > 0, `Uren`, 0 ) AS `Uren`,
    IF( count( `Minuten` ) > 0, `Minuten`, 0 ) AS `Minuten`
  FROM `Uren` 
    RIGHT JOIN `Project` 
            ON `Project`.`idProject` = `Uren`.`idProject` 
    RIGHT JOIN `Uursoort` 
            ON `Uursoort`.`idUursoort` = `Uren`.`idUursoort` 
  WHERE `idKlant` = 5 
    AND `Uren`.`Datum` > '2014-04-02' 
) results
GROUP BY `idProject`

Upvotes: 0

smoore4
smoore4

Reputation: 4866

Do this with your SUMs:

SELECT IFNULL(SUM(Uren),0),IFNULL(SUM(Minuten),0)

I have also had to do this, but with DB2 and DB2 is not standardized, to solve this same problem.

SELECT 0,0
UNION ALL
SELECT SUM(Uren), SUM(Minuten)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can do this by moving the where conditions into the on clause for all tables except the last one (because you are using right outer join:

SELECT coalesce(SUM(`Uren`), 0), coalesce(SUM(`Minuten`), 0)
FROM `Uren` RIGHT JOIN
     `Project` 
      ON `Project`.`idProject` = `Uren`.`idProject` AND
         `Uren`.`Datum` > '2014-04-02' RIGHT JOIN
     `Uursoort` 
      ON `Uursoort`.`idUursoort` = `Uren`.`idUursoort` 
WHERE `idKlant` = 5 AND 
GROUP BY `Uren`.`idProject`;

Personally, I find left outer join easier to follow ("Keep all the rows in the first table" versus "keep all the rows in the last table"), but the same principle applies: where conditions can turn outer joins into inner joins because outer joins generate unexpected NULL values.

You might also have to move idklant to an on clause. And I question why you are aggregating by uren.idProject, which might have NULL values.

Upvotes: 1

Related Questions