HELP WITH GROUP BY CLAUSE-URGENT

  • Hi ,Can somebody help me on this

    1.  when grouping on loss date, i want to group by the year, and not the month or day
    2.  for From_Date please i want to group by the month (2006-01) and not the day (2006-01-31)

    ------------------------------------------------------------------------------------

    CREATE procedure GE_Claim_Record_Counts

           @Month_of_file_filter datetime

    AS

    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    begin

    CREATE TABLE [dbo].[ClaimCounts] (

           [Month_of_file] [datetime] NULL ,

           [CalcAction] [varchar] (100) COLLATE

    SQL_Latin1_General_CP1_CI_AS NULL ,

           [TableValue] [varchar] (50) COLLATE

    SQL_Latin1_General_CP1_CI_AS NULL ,

           [CalculatedValue] [int] NULL

    ) ON [PRIMARY]

    end

    INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)

           SELECT @Month_of_file_filter as Month_of_file, 'Record Count by Loss Month' as CalcAction,

           Convert(Char(10),LossDate,111) as TableValue, COUNT(*)  as CalculatedValue

           FROM GE_Claim

           WHERE Month_of_file = @Month_of_file_filter

           GROUP BY LossDate

    INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)

           SELECT @Month_of_file_filter as Month_of_file, 'Record Count by Transaction Begin Month' as CalcAction,

           Convert(Char(10),From_Date,111) as TableValue, COUNT(*)  as CalculatedValue

           FROM GE_Claim

           WHERE Month_of_file = @Month_of_file_filter

           GROUP BY From_Date

    GO

  • Asim

    Have you tried

    GROUP BY YEAR(LossDate)

    and

    GROUP BY MONTH(From_Date)?

    John

  • Hi,

    if you want to GROUP BY some part of the date, the same part of date has to be displayed in the select list. You can't group by year, and then select precise date.

    For example, this would be by month (month and year, as you specified)

    SELECT  @Month_of_file_filter as Month_of_file,

     'Record Count by Loss Month' as CalcAction,

     Convert(Char(7),LossDate,111) as TableValue,

     COUNT(*)  as CalculatedValue

           FROM GE_Claim

           WHERE Month_of_file = @Month_of_file_filter

           GROUP BY Convert(Char(7),LossDate,111)

    You can use YEAR(LossDate) or Convert(Char(4),LossDate,111) to get only the year.

  • You need to group by the formula for TableValue:

    INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)

    SELECT

     @Month_of_file_filter AS Month_of_file

     ,'Record Count by Loss Month' AS CalcAction

     ,CONVERT(varchar(4), LossDate, 120) AS TableValue -- change here

     ,COUNT(*) AS CalculatedValue

    FROM GE_Claim

    WHERE Month_of_file = @Month_of_file_filter

    GROUP BY CONVERT(varchar(4), LossDate, 120)  -- change here

    INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)

    SELECT

     @Month_of_file_filter AS Month_of_file

     ,'Record Count by Transaction Begin Month' AS CalcAction

     ,CONVERT(varchar(7), From_Date, 120) as TableValue -- change here

     ,COUNT(*) as CalculatedValue

    FROM GE_Claim

    WHERE Month_of_file = @Month_of_file_filter

    GROUP BY CONVERT(varchar(7), From_Date, 120) -- change here

    ps GROUP BY is evaluated before SELECT so you cannot use the alias.

     

  • thanks for the help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply