Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Using a field alias for a CASE statement in a GROUP BY clause

    When I created a CASE statement (This is at work, Pat) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:

    ie,
    Code:
    SELECT fieldname1 =
                 CASE
                     WHEN condition THEN 'blah blah'
                     WHEN condition THEN 'blah blah'
                     WHEN condition THEN 'blah blah'
                     ELSE thisandthat
                 END
             , fieldname2
             , fieldname3
      FROM tablename1
      GROUP BY CASE
                     WHEN condition THEN 'blah blah'
                     WHEN condition THEN 'blah blah'
                     WHEN condition THEN 'blah blah'
                     ELSE thisandthat
                 END, , fieldname2, fieldname3
    
    etc.
    The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".

    Regards,

    Dave

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try:

    Code:
    SELECT * FROM (
    SELECT fieldname1 =
                 CASE
                     WHEN condition THEN 'blah blah'
                     WHEN condition THEN 'blah blah'
                     WHEN condition THEN 'blah blah'
                     ELSE thisandthat
                 END
             , fieldname2
             , fieldname3
      FROM tablename1) AS XXX
      GROUP BY CASE fieldname1, fieldname2, fieldname3
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    I tried what you told me and it works fine. However, when I further filter the results with a WHERE clause it blows up on me. I moved the alias to include the WHERE clause. Can you take a look at it? The error message is at the bottom. Thanks.

    ddave

    Code:
    actual query: 
    
    
    SELECT * 
      FROM ( 
    SELECT UserName = 
           CASE WHEN branch_num IN (225,  --Arkansas(AR) 
                                    95,222,223,269,311,383,435,457,  --Kansas(KS) 
                                    549,  --Maine(MA) 
                                    76,272,  --Mississippi(MS) 
                                    85,91,125,135,182,237,283,312,350,355,357,403,413,430, 
                                    433,469,576,577,  --New Jersey(NJ) 
                                    70,72,73,74,88,148,157,238,540,591,614,743,806,822,824,  --Virginia(VA) 
                                    578)  --West Virginia(WV)                               
                                    THEN 'Adrienne Torres' 
                WHEN branch_num IN (51,144,158,160,249,276,282,298,356,389,418,527,3560,3561)  --Georgia 
                                    THEN 'Alexis Bettonschinck' 
                WHEN branch_num IN (254,  --Deleware(DE) 
                                    192,228,449,539,759,  --Kentucky(KY) 
                                    35,215,227,328,359,360,447,464,476,506,516,523,  --Massachusetts(MA) 
                                    109,235,267,274,313,367,395,  --Missouri(MO) 
                                    48,53,78,198,208,212,286,287,353,398,440,478,572,610,  --North Carolina(NC) 
                                    173,186,257,317,571,  --South Carolina(SC) 
                                    56,200,218,384,462,483,485,593,627,820)  --Wisconsin(WI) 
                                    THEN 'Brendan O''Neil' 
                WHEN branch_num IN (239,  --Washington DC(DC) 
                                    40,41,43,45,75,79,89,102,119,137,175,178,191,229,236,241, 
                                    242,243,248,288,293,303,331,334,341,349,366,400,401,404,428, 
                                    432,436,437,509,510,586,587,620,741,755,823,858,860,869,  --Florida(FL) 
                                    3,36,87,127,149,161,179,233,362,386,422,536,  --Maryland(MD) 
                                    163,205,226,252,253,260,316,375,420,528,628,862)  --Michigan(MI) 
                                    THEN 'Candis Yamamoto' 
                WHEN branch_num IN (103,210,211,230,256,414,858,  --Alabama(AL) 
                                    55,219,369,621,1025,  --Minnesota(MN) 
                                    54,58,92,97,108,121,152,167,168,184,217,364,394, 
                                    396,431,442,580,581,  --Pennsylvania(PA) 
                                    216,  --Rhode Island(RI) 
                                    42,46,57,281,337,388,579,5790,5791)  --Tennessee(TN) 
                                    THEN 'Chris Arnett' 
                WHEN branch_num IN (352,  --Alaska(AK) 
                                    69,  --Hawaii(HI) 
                                    83,209,213,279,319,  --Louisiana(LA) 
                                    21,26,29,47,67,140,162,187,373,409)  --Washington(WA) 
                                    THEN 'Cynthia Leva' 
                WHEN branch_num IN (64,264,  --Connecticut(CT) 
                                    136,224,268,507,625,  --Iowa(IA) 
                                    142,165,206,262,310,468,538,617,816,817,818,  --Indiana(IN) 
                                    104,368,  --New Hampshire(NH) 
                                    37,84,131,141,214,250,277,289,290,314,365,629,1410, 
                                    1411,1412,1413,1414)  --New York(NY) 
                                    THEN 'Eric Pugh' 
                WHEN branch_num IN (60,61,62,204,275,295,309,325,345,393,417,471,544,551,  --Arizona(AZ) 
                                    2,4,5,6,7,8,9,10,12,13,15,17,18,19,20,22,23,25,27,28,30,49, 
                                    59,65,66,98,100,101,105,116,122,124,129,130,133,146,155,174, 
                                    177,181,245,246,259,265,285,302,318,339,346,407,429,444,445, 
                                    446,455,461,463,470,472,479,480,481,482,508,515,521,529,537, 
                                    545,548,550,562,565,583,584,594,595,597,598,599,615,616,742, 
                                    744,756,814,815,819,854,872,1010,2460,4820,  --California(CA) 
                                    81,106,115,126,132,150,183,294,326,374,376,416,454,456,458,459, 
                                    466,467,518,519,568,569,623,625,  --Illinois(IL) 
                                    202,622,  --Nebraska(NE) 
                                    16,96,323,330,372,473,1005,4734,  --Nevada(NV) 
                                    123,154,348,561,749)  --Oregon(OR) 
                                    THEN 'Jennifer Tran' 
                WHEN branch_num IN (52,156,170,247,266,273,291,296,351,371,379,391, 
                                    412,477,520,566,574,575,766,  --Colorado(CO) 
                                    194,263,344,573,  --Idaho(ID) 
                                    380,  --Montana(MT) 
                                    201,280,465,  --New Mexico(NM) 
                                    221,327,381,382,392,426,  --Oklahoma(OK) 
                                    80,263,329,333,347,434,452,  --Utah(UT) 
                                    567)  --Wyoming(WY) 
                                    THEN 'Joe Garibay' 
                WHEN branch_num IN (32,34,38,128,134,147,151,169,195,196,197,220,232,234,251,261,270, 
                                    306,307,315,324,335,342,343,358,377,402,410,421,425,453,475,486, 
                                    531,552,555,588,592,762,763,764,765,771,861,1015)  --Texas(TX) 
                                    THEN 'Nate Engle' 
                WHEN branch_num IN (33,112,117,143,164,185,189,190,255,271,292,304,320,321,322,411,415, 
                                    423,427,438,439,487,505,530,618,  --Ohio(OH) 
                                    855,878,3501,3503,3505,3506,3508)  -- 
                                    THEN 'Teresa Diokno' 
                WHEN branch_num IN (484)  --Construction Lending(Entire U.S.) 
                                    THEN 'w-Construction Lending' 
                WHEN branch_num IN (850)  --Employee Loans(Entire U.S.) 
                                    THEN 'x-Employee Loans' 
                WHEN branch_num IN (877)  --Central Processing Unit(Entire U.S.) 
                                    THEN 'y-Central Processing' 
                ELSE 'z-other branches' 
           END 
         , COUNT(*) AS Wires 
      FROM edge_holding 
      WHERE request_type = 'W' 
        AND post <> 'FUTURE FUNDING' 
        AND ( 
             branch_num IN (19,20,9031)   --shortened this list to post on dbforums
            ) ) AS xyz 
      GROUP BY UserName, Wires 
      ORDER BY UserName
    Server: Msg 8118, Level 16, State 1, Line 1
    Column 'edge_holding.branch_num' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Problem is with the derived (the inner select) table...

    you can't do a count with the column without a group by

    try this

    Code:
    SELECT UserName, Count(*) AS Wires
      FROM ( 
    SELECT UserName = 
           CASE WHEN branch_num IN (225,  --Arkansas(AR) 
                                    95,222,223,269,311,383,435,457,  --Kansas(KS) 
                                    549,  --Maine(MA) 
                                    76,272,  --Mississippi(MS) 
                                    85,91,125,135,182,237,283,312,350,355,357,403,413,  430, 
                                    433,469,576,577,  --New Jersey(NJ) 
                                    70,72,73,74,88,148,157,238,540,591,614,743,806,822  ,824,  --Virginia(VA) 
                                    578)  --West Virginia(WV)                               
                                    THEN 'Adrienne Torres' 
                WHEN branch_num IN (51,144,158,160,249,276,282,298,356,389,418,527,35  60,3561)  --Georgia 
                                    THEN 'Alexis Bettonschinck' 
                WHEN branch_num IN (254,  --Deleware(DE) 
                                    192,228,449,539,759,  --Kentucky(KY) 
                                    35,215,227,328,359,360,447,464,476,506,516,523,  --Massachusetts(MA) 
                                    109,235,267,274,313,367,395,  --Missouri(MO) 
                                    48,53,78,198,208,212,286,287,353,398,440,478,572,6  10,  --North Carolina(NC) 
                                    173,186,257,317,571,  --South Carolina(SC) 
                                    56,200,218,384,462,483,485,593,627,820)  --Wisconsin(WI) 
                                    THEN 'Brendan O''Neil' 
                WHEN branch_num IN (239,  --Washington DC(DC) 
                                    40,41,43,45,75,79,89,102,119,137,175,178,191,229,2  36,241, 
                                    242,243,248,288,293,303,331,334,341,349,366,400,40  1,404,428, 
                                    432,436,437,509,510,586,587,620,741,755,823,858,86  0,869,  --Florida(FL) 
                                    3,36,87,127,149,161,179,233,362,386,422,536,  --Maryland(MD) 
                                    163,205,226,252,253,260,316,375,420,528,628,862)  --Michigan(MI) 
                                    THEN 'Candis Yamamoto' 
                WHEN branch_num IN (103,210,211,230,256,414,858,  --Alabama(AL) 
                                    55,219,369,621,1025,  --Minnesota(MN) 
                                    54,58,92,97,108,121,152,167,168,184,217,364,394, 
                                    396,431,442,580,581,  --Pennsylvania(PA) 
                                    216,  --Rhode Island(RI) 
                                    42,46,57,281,337,388,579,5790,5791)  --Tennessee(TN) 
                                    THEN 'Chris Arnett' 
                WHEN branch_num IN (352,  --Alaska(AK) 
                                    69,  --Hawaii(HI) 
                                    83,209,213,279,319,  --Louisiana(LA) 
                                    21,26,29,47,67,140,162,187,373,409)  --Washington(WA) 
                                    THEN 'Cynthia Leva' 
                WHEN branch_num IN (64,264,  --Connecticut(CT) 
                                    136,224,268,507,625,  --Iowa(IA) 
                                    142,165,206,262,310,468,538,617,816,817,818,  --Indiana(IN) 
                                    104,368,  --New Hampshire(NH) 
                                    37,84,131,141,214,250,277,289,290,314,365,629,1410  , 
                                    1411,1412,1413,1414)  --New York(NY) 
                                    THEN 'Eric Pugh' 
                WHEN branch_num IN (60,61,62,204,275,295,309,325,345,393,417,471,544,  551,  --Arizona(AZ) 
                                    2,4,5,6,7,8,9,10,12,13,15,17,18,19,20,22,23,25,27,  28,30,49, 
                                    59,65,66,98,100,101,105,116,122,124,129,130,133,14  6,155,174, 
                                    177,181,245,246,259,265,285,302,318,339,346,407,42  9,444,445, 
                                    446,455,461,463,470,472,479,480,481,482,508,515,52  1,529,537, 
                                    545,548,550,562,565,583,584,594,595,597,598,599,61  5,616,742, 
                                    744,756,814,815,819,854,872,1010,2460,4820,  --California(CA) 
                                    81,106,115,126,132,150,183,294,326,374,376,416,454  ,456,458,459, 
                                    466,467,518,519,568,569,623,625,  --Illinois(IL) 
                                    202,622,  --Nebraska(NE) 
                                    16,96,323,330,372,473,1005,4734,  --Nevada(NV) 
                                    123,154,348,561,749)  --Oregon(OR) 
                                    THEN 'Jennifer Tran' 
                WHEN branch_num IN (52,156,170,247,266,273,291,296,351,371,379,391, 
                                    412,477,520,566,574,575,766,  --Colorado(CO) 
                                    194,263,344,573,  --Idaho(ID) 
                                    380,  --Montana(MT) 
                                    201,280,465,  --New Mexico(NM) 
                                    221,327,381,382,392,426,  --Oklahoma(OK) 
                                    80,263,329,333,347,434,452,  --Utah(UT) 
                                    567)  --Wyoming(WY) 
                                    THEN 'Joe Garibay' 
                WHEN branch_num IN (32,34,38,128,134,147,151,169,195,196,197,220,232,  234,251,261,270, 
                                    306,307,315,324,335,342,343,358,377,402,410,421,42  5,453,475,486, 
                                    531,552,555,588,592,762,763,764,765,771,861,1015)  --Texas(TX) 
                                    THEN 'Nate Engle' 
                WHEN branch_num IN (33,112,117,143,164,185,189,190,255,271,292,304,32  0,321,322,411,415, 
                                    423,427,438,439,487,505,530,618,  --Ohio(OH) 
                                    855,878,3501,3503,3505,3506,3508)  -- 
                                    THEN 'Teresa Diokno' 
                WHEN branch_num IN (484)  --Construction Lending(Entire U.S.) 
                                    THEN 'w-Construction Lending' 
                WHEN branch_num IN (850)  --Employee Loans(Entire U.S.) 
                                    THEN 'x-Employee Loans' 
                WHEN branch_num IN (877)  --Central Processing Unit(Entire U.S.) 
                                    THEN 'y-Central Processing' 
                ELSE 'z-other branches' 
           END 
      FROM edge_holding 
      WHERE request_type = 'W' 
        AND post <> 'FUTURE FUNDING' 
        AND ( 
             branch_num IN (19,20,9031)   --shortened this list to post on dbforums
            ) ) AS xyz 
      GROUP BY UserName
      ORDER BY UserName
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    94
    Put the case statement into a scalar function so that your select can read:

    SELECT UserName = dbo.MyFunction(branch_num), COUNT(*) AS Wires

    ....And :

    GROUP BY branch_num

    The order by will work as expected

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    Thank you both. I will give it a shot.

    ddave

Posting Permissions

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