I have a sql query that does not provide the correct counts.

select location.name as "location Name",
count ( case when incident.location_type = 138 AND
incident_detail.witnessed = TRUE then 1 else null end) as "Fall
witnessed"
from incident, incident_detail, location
where location.id = incident.id

The is only 1 incident in my test DB that has a
incident_detail.witnessed set to true, so the outut should be

location name | Fall Witnessed
==================================
Floor | 0
Room | 0
Hallway | 1


But I get instead ;

location name | Fall Witnessed
==================================
Floor | 1
Room | 1
Hallway | 1

What am I doing wrong here?
Is it becasue the query is from mutlitple tables?

Let me know i you can help

Thanks

"Bigjim" <bigjim.still (AT) rogers (DOT) com> schreef in bericht
news:1169835949.275145.213640 (AT) q2g2000cwa (DOT) googlegroups.com...
adding ' and incident.id = incident_detail.id'
may cure

To avoid such unnoticed drop-outs of code, consider using explicite join
syntax
from incident
join location on( incident.id = location.id )
join incident_detail on( incident.id = incident_detail.id )


HansH

Hi,

On 2007-01-26 19:25, Bigjim wrote:

Probably you should try to use "sum" instead of "count".

hth,
Benjamin

Thanks HansH !, that is exactly what it was.
I learn something new everyday, and that's what keeps me young !!

On Jan 26, 7:04 pm, "HansH" <h... (AT) invalid (DOT) invalid> wrote: