Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: does the group by have to include all fields from the SELECT clause?

    hey all,

    say i have the following function

    Code:
    SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, 
    F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, 
    F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, 
    F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, 
    F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR
    
    FROM GLF_CHART_ACCT 
    
    INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1) 
    AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2) 
    
    GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, 
    F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, 
    F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, 
    F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI, 
    F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR,
    GLF_CHART_ACCT.SELN_TYPE1_CODE
    
    HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
    AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;
    I get errors if not all the fields are included in the group by clause.

    what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??

    Cheers,

    Justin

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, in microsoft sql server all non-aggregate expressions in the SELECT must also be in the GROUP BY

    may i ask why you are grouping? you don't have any aggregates, and all your HAVING conditions should actually be in the WHERE clause
    Code:
    SELECT GLF_CHART_ACCT.DESCR1
         , F1ADR_ADDRESS.ADDR1
         , F1ADR_ADDRESS.ADDR2
         , F1ADR_ADDRESS.ADDR3
         , F1ADR_ADDRESS.ADDR_CITY
         , F1ADR_ADDRESS.ADDR_STATE
         , F1ADR_ADDRESS.POST_CODE
         , F1ADR_ADDRESS.PHONE_NBR
         , F1ADR_ADDRESS.FAX_NBR
         , F1ADR_ADDRESS.EMAIL_ADDR_NAME
         , F1ADR_ADDRESS.CONTACT_NAME
         , F1ADR_ADDRESS.CONTACT_TITLE
         , GLF_CHART_ACCT.ACCNBRI
         , F1ADR_ADDRESS.ENTITY_UNIQUE_NBR
      FROM GLF_CHART_ACCT 
    INNER 
      JOIN F1ADR_ADDRESS 
        ON F1ADR_ADDRESS.ENTITY_KEY1 = GLF_CHART_ACCT.CHART_NAME 
       AND F1ADR_ADDRESS.ENTITY_KEY2 = GLF_CHART_ACCT.ACCNBRI
     WHERE GLF_CHART_ACCT.CHART_NAME = 'ARCHART' 
       AND GLF_CHART_ACCT.DESCR1 <> '' 
       AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
       AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2005
    Posts
    55
    Quote Originally Posted by r937
    yes, in microsoft sql server all non-aggregate expressions in the SELECT must also be in the GROUP BY

    may i ask why you are grouping? you don't have any aggregates, and all your HAVING conditions should actually be in the WHERE clause
    Code:
    SELECT GLF_CHART_ACCT.DESCR1
         , F1ADR_ADDRESS.ADDR1
         , F1ADR_ADDRESS.ADDR2
         , F1ADR_ADDRESS.ADDR3
         , F1ADR_ADDRESS.ADDR_CITY
         , F1ADR_ADDRESS.ADDR_STATE
         , F1ADR_ADDRESS.POST_CODE
         , F1ADR_ADDRESS.PHONE_NBR
         , F1ADR_ADDRESS.FAX_NBR
         , F1ADR_ADDRESS.EMAIL_ADDR_NAME
         , F1ADR_ADDRESS.CONTACT_NAME
         , F1ADR_ADDRESS.CONTACT_TITLE
         , GLF_CHART_ACCT.ACCNBRI
         , F1ADR_ADDRESS.ENTITY_UNIQUE_NBR
      FROM GLF_CHART_ACCT 
    INNER 
      JOIN F1ADR_ADDRESS 
        ON F1ADR_ADDRESS.ENTITY_KEY1 = GLF_CHART_ACCT.CHART_NAME 
       AND F1ADR_ADDRESS.ENTITY_KEY2 = GLF_CHART_ACCT.ACCNBRI
     WHERE GLF_CHART_ACCT.CHART_NAME = 'ARCHART' 
       AND GLF_CHART_ACCT.DESCR1 <> '' 
       AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
       AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%'
    It was from a previous dev. Thats why I was sorta like, why are we using a Group By & having rather than just straight WHERE...

    So, an aggregate function would be SUM(), and if I was to use the SUM (or any other function that uses more than one field), i need the group by.

    Cool, Thanks for that. I'm now going to rewrite all those stored procs...

  4. #4
    Join Date
    Aug 2005
    Posts
    55
    also, does it matter what order the fields in the GROUP BY clause go in?

    If so, what difference does it make to the results?

    Cheers,
    Justin

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it doesn't matter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    And some table aliases like G.CHART_NAME and F.ENTITY_KEY2 would be easier on the eyes and fingers.
    Don't Bogart That Squishee!

Posting Permissions

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