Post reply

Multiple columns at GROUP BY clause in CASE clause

  • May 20, 2010 at 3:27 am

    #232676

    Dear All,

    Please help me, I want to know that can I use multiple columns for GROUP BY clause after using CASE in GROUP BY? Here is my query.

    When I execute my query error occour --> Incorrect syntax near ',' <-- at my GROUP BY clause.

    declare @num int

    set @num= (select ctrqty from ##temp4temp where ctrqty>1)

    print @num

    SELECT Distinct b.bl_no,b.laden_date , b.container_code , b.shipper_name ,b.consignee_name,sum(b.qty) As FLQty

    FROM docdbdwh.dbo.mastercentral_bl b

    INNER JOIN Credo.dbo.account_code ON b.account_code COLLATE DATABASE_DEFAULT = Credo.dbo.account_code.account_code COLLATE DATABASE_DEFAULT

    inner JOIN ##tmp_ctr2005111852 c ON b.bl_no COLLATE DATABASE_DEFAULT = c.bl_no COLLATE DATABASE_DEFAULT

    And b.container_code COLLATE DATABASE_DEFAULT = c.container_code COLLATE DATABASE_DEFAULT

    AND b.vessel_code COLLATE DATABASE_DEFAULT = c.vesselcode COLLATE DATABASE_DEFAULT

    And b.voyage_no COLLATE DATABASE_DEFAULT = c.voyageno COLLATE DATABASE_DEFAULT

    WHERE ( upper(Credo.dbo.account_code.account_name) like '%FREIGHT%' and upper(Credo.dbo.account_code.account_name) not like '%FREIGHT REBATE%' )

    and b.bl_type <> 'S' and b.bl_no Not In (SELECT bl_no FROM docdbdwh.dbo.MasterCentral_BL WHERE BL_ISSUE = 'IDBDO' AND BL_Type = 'T' )

    group by case @num

    WHEN 2 THEN

    (b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name,c.status,b.qty)

    Else

    (b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name)

    end

    Thanks and Best Regards

  • May 20, 2010 at 7:08 am

    #1170088

    you need to modify your group by stmt

    group by b.bl_no, b.laden_date,b.container_code,b.shipper_name,b.consignee_name

    , case when @num = 2 then c.status end

    , case when @num = 2 then b.qty end

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • May 20, 2010 at 7:37 pm

    #1170554

    Thank you so much, the query work fine with your help. 🙂

    But, my idiot wrong logic doesn't make result come out what I want.

Viewing 3 posts - 1 through 2 (of 2 total)

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