Query with total count

[Mario Krsnic]

Hello everybody,
I have this query:
SELECT MyDate, count(Diagnosis ) AS MyCount
FROM TblTherapy
WHERE Diagnosis like '*myText*'
GROUP BY MyDate;

I get so the count of diagnosis on the certain day. But I would like to have
a
total count of diagnosis on certain day.
How to get it?

Thank for your ideas
Mario Krsnic

 

[StrayBullet via AccessMonster.com]

In a separate query. Removing the WHERE clause will count all diagnosises on
each day.

 

[Mario Krsnic]

Thank you.

In a separate query. Removing the WHERE clause will count all diagnosises
on
each day.

But I do not want to count ALL dignosises on each day, but specific
diagnosises on each day.
I need to know how many diagnosises headache on 1.april, on 2. april etc,
how many diagnosises stomach ache on 1.april, on 2.april etc.
Cheers
Mario

 

[Michael Gramelspacher]

Thank you.

In a separate query. Removing the WHERE clause will count all diagnosises
on
each day.

But I do not want to count ALL dignosises on each day, but specific
diagnosises on each day.
I need to know how many diagnosises headache on 1.april, on 2. april etc,
how many diagnosises stomach ache on 1.april, on 2.april etc.
Cheers
Mario
SELECT t.diagnosis_date,
t.diagnosis_name,
COUNT(* ) AS [diagnosis count]
FROM therapy AS t
GROUP BY t.diagnosis_date,t.diagnosis_name;

diagnosis_date diagnosis_name Diagnosis Count
3/31/2007 fever 2
3/31/2007 headache 2
3/31/2007 vomitting 1
4/1/2007 fever 2
4/1/2007 headache 1