Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: 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.

  2. #2
    Join Date
    Mar 2004
    Posts
    31
    I should give some more info to make the analysis easier...

    My raw data looks like this

    Date Type
    20040101 Meal
    20040101 Meal
    20040101 Meal

    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?

    Thanks. Sorry I wasn't more clear.

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    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)

  4. #4
    Join Date
    Mar 2004
    Posts
    31
    Yes, I want to count one meal per day, however I still want the other line items to show as zero, because they have costs.

    I'll try your SQL, but won't it drop the other 2 line items?

    Thanks.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select date expression
    , count(*) as numberofmeals
    , sum(costs)/count(*) as averagecostofmeal
    from yourtable
    group by date expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    another solution :
    select Date, Type into ##temp from yourtable group by Date, Type;
    select Type,count(type) from ##temp group by type;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •