Return zero if query returns nothing?

[nickj]

Hi,

I have the following query, which does not return any results.

select (sum(t.minutes) / 60),d.fiscalmonthnum from kpifntime t
	inner join kpiddate d on t.datekey=d.datekey
	inner join kpidacts a on t.activitykey=a.activitykey
	inner join kpidfees f on t.feeearnerkey=f.feeearnerkey	
where a.activitycode in (11,12,13,14,15,16,17,18)
and d.fiscalmonthnum=11
and d.fiscalyearnum=2009
and t.feeearnerkey=520
group by fiscalmonthnum;

If the sum of t.minutes does not return a result, I need the query to return the integer 0.

I've tried this using the IFNULL() function, but I don't think this is correct as the result being returned from the query is not null, just blank.

Any ideas guys?

[ShawnCplus]

Hi,

I have the following query, which does not return any results.

select (sum(t.minutes) / 60),d.fiscalmonthnum from kpifntime t
	inner join kpiddate d on t.datekey=d.datekey
	inner join kpidacts a on t.activitykey=a.activitykey
	inner join kpidfees f on t.feeearnerkey=f.feeearnerkey	
where a.activitycode in (11,12,13,14,15,16,17,18)
and d.fiscalmonthnum=11
and d.fiscalyearnum=2009
and t.feeearnerkey=520
group by fiscalmonthnum;

If the sum of t.minutes does not return a result, I need the query to return the integer 0.

I've tried this using the IFNULL() function, but I don't think this is correct as the result being returned from the query is not null, just blank.

Any ideas guys?

As far as I know there's no way to halt a SELECT statement mid-query. You'd either have to write a stored procedure or have whatever language you're using to interact with MySQL handle this logic.

[Atli]

Hi.

You could try the IF function:

SELECT
    IF(SUM(number) IS NOT NULL, SUM(number), 0) AS `Sum`
FROM myTable;