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.
I should give some more info to make the analysis easier...
My raw data looks like this
I want the SQL to add a Count column and the Fact table data should look like this (one count per day).
Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0
The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?
I still don't understand what you are trying to do. To get a count of meals, why don't you just
SELECT COUNT(*) From Table WHERE Type = 'Meal'
Do you want to count the number of days that have one meal so that if a single day has multiple meals, it just counts as one:
SELECT COUNT(*) From Table WHERE Type = 'Meal' GROUP BY datepart(year, Date), datepart(dayofyear, Date)