Loading ...
Sorry, an error occurred while loading the content.
 

Doing a COUNT per month on a Type of Event

Expand Messages
  • jason_c_ellis
    This one is racking my brain. Any help would be great. I am trying to get a query that will do a count on an event per month. So the data it will give me is
    Message 1 of 3 , Mar 29, 2004
      This one is racking my brain. Any help would be great. I am trying
      to get a query that will do a count on an event per month. So the
      data it will give me is grouped by region giving the count of dinners
      performed per month. This is all coming from one table.

      select
      e.netgainid,
      e.eventtype,
      e.therapy,
      e.numattendees,
      e.startdate,
      e.repid,
      e.region

      from
      kcibevent e

      where
      e.eventtype = 'Local Vac Dinner' and
      ((e.region) like funct_to_search(RegionIN) or RegionIN IS NULL) and
      (e.startdate between BeginDateIN and EndDateIN)
      group by
      e.startdate,
      e.region,
      e.eventtype,
      e.therapy,
      e.numattendees,
      e.repid,
      e.netgainid

      Netgainid is the key in this table and doing a count on this field is
      the best way to go. I am not sure on how to pull the data out per
      month though.

      And suggestions?
    • Razvan Socol
      Generally speaking, it should be something like: SELECT region, YEAR(eventdate) as yr, MONTH(eventdate) as mo, COUNT(*) as cnt FROM the_table WHERE
      Message 2 of 3 , Mar 30, 2004
        Generally speaking, it should be something like:

        SELECT region, YEAR(eventdate) as yr, MONTH(eventdate) as mo,
        COUNT(*) as cnt FROM the_table WHERE eventtype='something'
        GROUP BY region, YEAR(date), MONTH(date)

        Razvan
      • Michael Weiss
        Group by Month(e.startdate)...should do the trick for you...if your data spans over one year you would want to group by Month(e.startdate),
        Message 3 of 3 , Mar 30, 2004
          Group by Month(e.startdate)...should do the trick for you...if your data
          spans over one year you would want to group by Month(e.startdate),
          Year(e.startdate)...
          Hth,
          Michael

          -----Original Message-----
          From: jason_c_ellis [mailto:jason_c_ellis@...]
          Sent: Monday, March 29, 2004 9:26 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SQLQueriesNoCode] Doing a COUNT per month on a Type of Event

          This one is racking my brain. Any help would be great. I am trying
          to get a query that will do a count on an event per month. So the
          data it will give me is grouped by region giving the count of dinners
          performed per month. This is all coming from one table.

          select
          e.netgainid,
          e.eventtype,
          e.therapy,
          e.numattendees,
          e.startdate,
          e.repid,
          e.region

          from
          kcibevent e

          where
          e.eventtype = 'Local Vac Dinner' and
          ((e.region) like funct_to_search(RegionIN) or RegionIN IS NULL) and
          (e.startdate between BeginDateIN and EndDateIN)
          group by
          e.startdate,
          e.region,
          e.eventtype,
          e.therapy,
          e.numattendees,
          e.repid,
          e.netgainid

          Netgainid is the key in this table and doing a count on this field is
          the best way to go. I am not sure on how to pull the data out per
          month though.

          And suggestions?






          Yahoo! Groups Links
        Your message has been successfully submitted and would be delivered to recipients shortly.