SQL Server 2008 Issue With GROUP BY

Question

SELECT     
  ETRN_MFTransactionGroup.FolioNumber,
  ETRN_MFTransactionGroup.PrimaryApplicantContactID,
  ETRN_MFTransaction.PK_TransactionGroupID AS Expr1, 
  ETRN_MFTransaction.PK_SchemeProductID,
  ETRN_MFTransaction.Units,
  PA_E_CUSTOMER.dbo.ECUS_Contact.PK_ContactID, 
  PA_E_CUSTOMER.dbo.ECUS_Contact.UserID,
  PA_E_CUSTOMER.dbo.ECUS_Contact.FName,
  PA_E_CUSTOMER.dbo.ECUS_Contact.MName, 
  PA_E_CUSTOMER.dbo.ECUS_Contact.LName,     
  PA_E_INSTRUMENTS.dbo.EINS_MFSchemeProduct.PK_SchemeProductID AS Expr2, 
  PA_E_INSTRUMENTS.dbo.EINS_MFSchemeProduct.RegistrarCode, 
  ETRN_MFTransactionGroup.PK_TransactionGroupID
FROM
  ETRN_MFTransactionGroup 
INNER JOIN
  ETRN_MFTransaction ON
  ETRN_MFTransactionGroup.PK_TransactionGroupID =  ETRN_MFTransaction.PK_TransactionGroupID
 INNER JOIN
   PA_E_CUSTOMER.dbo.ECUS_Contact ON           
     ETRN_MFTransactionGroup.PrimaryApplicantContactID =                     
      PA_E_CUSTOMER.dbo.ECUS_Contact.PK_ContactID
 INNER JOIN
   PA_E_INSTRUMENTS.dbo.EINS_MFSchemeProduct ON 
     ETRN_MFTransaction.PK_SchemeProductID = 
         PA_E_INSTRUMENTS.dbo.EINS_MFSchemeProduct.PK_SchemeProductID
**GROUP BY
    ETRN_MFTransactionGroup.FolioNumber,**
    ETRN_MFTransactionGroup.PrimaryApplicantContactID,                  
    ETRN_MFTransaction.PK_TransactionGroupID,
    ETRN_MFTransaction.PK_SchemeProductID,
    ETRN_MFTransaction.Units,
    PA_E_CUSTOMER.dbo.ECUS_Contact.PK_ContactID, 
    PA_E_CUSTOMER.dbo.ECUS_Contact.UserID,
    PA_E_CUSTOMER.dbo.ECUS_Contact.FName,
    PA_E_CUSTOMER.dbo.ECUS_Contact.MName, 
    PA_E_CUSTOMER.dbo.ECUS_Contact.LName, 
    PA_E_INSTRUMENTS.dbo.EINS_MFSchemeProduct.PK_SchemeProductID, 
    PA_E_INSTRUMENTS.dbo.EINS_MFSchemeProduct.RegistrarCode,  
    ETRN_MFTransactionGroup.PK_TransactionGroupID

The query above works absolutely fine but i need to group only by FolioNumber (i.e - ETRN_MFTransactionGroup.FolioNumber). Grouping by rest of the fields not required at all!

You have to group on everything that you want to select from the table, that you don't use aggregates to calculate. If you group on FolioNumber only, you can only select that single field. — Guffa, Jun 18 '13 at 11:59
as @Guffa said and to further expand, if everything is an inner join, why do you need to group by at all? If you have multiple folio number records that a distinct cannot handle (assuming that is the right logic), then a group by is the wrong solution for this set at this time — Wayne, Jun 18 '13 at 12:11
If you actually want one row per `ETRN_MFTransactionGroup.FolioNumber` based on `max(date)` or `max(id)` you might find this helpful: [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Nikola Markovinović, Jun 18 '13 at 12:18
Please don't SHOUT when typing your questions. It's harder to read, and won't get you help any faster. Also, it helps when you take time to properly format your code, which again makes it easier to read and understand. Thanks. — Ken White, Jun 18 '13 at 12:37

score 0 · Accepted Answer · answered Jun 18 '13 at 12:02

0

If you don't want to group on the other fields, you need some aggregate function to tell SQL how to treat the multiple records. MAX, or MIN work well for varchar fields. SUM, AVERAGE work well for numerics.

answered Jun 18 '13 at 12:02

Bill Gregg

7,067
2
22
39

SQL Server 2008 Issue With GROUP BY

1 Answers1