count and group by

A

ayaari

I have a query that group by distinct name from a table and count how many
arraived to some place (arraived =1 , count how many 1 for each name) it
works great , only if a name has not have at least one "1" it doesnt show him
on the list.
i want it to show name and in the field arraived show "0" or nothing null.
this is the query:
SELECT SEPTEMBER10.mazmin_name, Count(SEPTEMBER10.arraived) AS
countarraived
FROM SEPTEMBER10
GROUP BY SEPTEMBER10.mazmin_name, SEPTEMBER10.arraived
HAVING (((SEPTEMBER10.arraived)=1));
who can help?
thanks in advance
alon
 
J

John Vinson

I have a query that group by distinct name from a table and count how many
arraived to some place (arraived =1 , count how many 1 for each name) it
works great , only if a name has not have at least one "1" it doesnt show him
on the list.
i want it to show name and in the field arraived show "0" or nothing null.
this is the query:
SELECT SEPTEMBER10.mazmin_name, Count(SEPTEMBER10.arraived) AS
countarraived
FROM SEPTEMBER10
GROUP BY SEPTEMBER10.mazmin_name, SEPTEMBER10.arraived
HAVING (((SEPTEMBER10.arraived)=1));
who can help?
thanks in advance
alon
Click to expand...

You'll need to join this query to a table containing one record for
each name, using a Left Outer Join.

If you have tables named SEPTEMBER10 and the like... it's almost
certain that you're on the wrong track! Storing data (such as a date)
in a tablename is a sure way to a difficult, non-normalized structure.
If this is the event date, consider instead having one big Events
table with fields mazmin_name (or, better, mazmin_ID linked to the
primary key of the table containing names), EventDate (#10-Sep-2005#
for example), Arrived, and any other needed fields.


John W. Vinson[MVP]
 
Top