Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.This is tough to help you do. The problem is that we (or I at least) don't understand your schema, so I'm not sure what you've got stored how.

One thing that I'd strongly suggest is to avoid using reserved words like "Count". You can use them, but it makes everything more work. In this case, I'd suggest using meal_count because it avoids the collision with a reserved word and it is more meaningful to some poor bozo like me that might try to help you!

-PatP|||This does not help. I was only giving you an example, I am using the 'Count' for the column title only, and am returning the same rows if I change the column title. I want to edit the SQL so I only count the number of days they had a meal charged.

Thanks.|||I'm sorry, I was trying to explain that I didn't know enough about your problem to help. If you can post the CREATE TABLE statements for your tables, and the SELECT statement (all of it), then I could get a lot closer. As it is, I don't know enough to give you anything more than guesses.

-PatP