title: SQL Server : aggregate functions and GROUP BY in long queries
body: <p>I was writing a query the other day in SQL Server, and had to get a sum of a column added to the query.</p>
  
  <p>Now, the query I had to add the sum column already had about 20 selected columns.</p>
  
  <p>In order for the sum (aggregate function) to work, I had to add <code>GROUP BY</code> for each pre selected column.</p>
  
  <p>This ended up as an extremely tedious and boring task (see the query below). I had to add about 50 new lines to the query just to get SUM for one column to work.</p>
  
  <p>Is there a way to automate writing such queries using SQL Server Management Studio?</p>
  
  <p>Is this the same with other databases (such as PL/SQL, Oracle, MySQL?)</p>
  
  <p>This is what the query looks like.</p>
  
  <pre><code>SELECT 
         mvLogisticContracts.[mvLogisticContract_id]
        ,mvLogisticContracts.[CONTRACTNUMBER] 
        ,mvLogisticContracts.[CUSTNMBR]
        ,mvLogisticContracts.[DateCreated]
        ,mvLogisticContracts.[TruckAllocatedTotal]      ,mvLogisticContracts.[mvLOgisticAddress_id_StartOriginal]
            ,mvLogisticContracts.[mvLogisticAddress_id_StartOriginal]       ,mvLogisticContractLineItems.[LineItemQuantity]     ,mvLogisticContractLineItems.[LineItemNo]
            ,mvLogisticContractLineItems.[OffloadRequired]
            ,mvLogisticContractLineItems.[CarrierRequested]     ,mvLogisticContractLineItems.[RequestedSerialNo]
            ,mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
            ,mvLogisticContractLineItems.[DateReadyBy]      ,mvLogisticContractLineItems.[DateCustomerRequested],
            mvLogisticContractLineItems.[mvLogisticContractLineItem_id]
        ,mvLogisticSalespersons.[FirstName],mvLogisticSalespersons.[LastName],
        mvLogisticServiceTypes.ServiceType,
        mvLogisticStatuses.Description,
        mvLogisticSKUs.[SKU],       mvLogisticSKUs.[Length],
        a1.CITY as \"CityStart\", a1.AddressName as \"AddressNameStart\", a1.Address1 as \"Address1Start\", a1.STATE as \"StateStart\", a1.ZIP as \"ZipStart\",
        a2.CITY as \"CityEnd\", a2.AddressName as \"AddressNameEnd\", a2.Address1 as \"Address1End\", a2.STATE as \"StateEnd\", a2.ZIP as \"ZipEnd\",
        mvLogisticContracts.[mvLogisticAddress_id_Start] AS aa1,
        mvLogisticContracts.[mvLogisticAddress_id_End] AS aa2,
        SUM(mvLogisticReleases.ReleaseQuantity) as ReleaseQtySum
              FROM mvLogisticContractLineItems
              LEFT JOIN mvLogisticContracts ON mvLogisticContractLineItems.mvLogisticContract_id = mvLogisticContracts.mvLogisticContract_id
              LEFT JOIN mvLogisticSalespersons ON mvLogisticContracts.[mvLogisticSalesperson_id] = mvLogisticSalespersons.[mvLogisticSalesperson_id]
              LEFT JOIN mvLogisticServiceTypes ON mvLogisticContractLineItems.mvLogisticServiceType_id = mvLogisticServiceTypes.mvLogisticServiceType_id
              LEFT JOIN mvLogisticStatuses ON mvLogisticContractLineItems.mvLogisticStatus_id = mvLogisticStatuses.mvLogisticStatus_id
              LEFT JOIN mvLogisticSKUs ON mvLogisticContractLineItems.[SKU_id] = mvLogisticSKUs.[SKU_id]
              LEFT JOIN mvLogisticAddresses a1 ON a1.[mvLogisticAddress_id] = mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
              LEFT JOIN mvLogisticAddresses a2 ON a2.[mvLogisticAddress_id] = mvLogisticContractLineItems.[mvLogisticAddress_id_End]
              /*LEFT JOIN mvLogisticAddresses a1 ON a1.[mvLogisticAddress_id] = mvLogisticContracts.[mvLogisticAddress_id_Start]
              LEFT JOIN mvLogisticAddresses a2 ON a2.[mvLogisticAddress_id] = mvLogisticContracts.[mvLogisticAddress_id_End]*/
              LEFT JOIN mvLogisticReleases ON mvLogisticContractLineItems.mvLogisticContractLineItem_id = mvLogisticReleases.mvLogisticContractLineItem_id
              GROUP BY
              mvLogisticContracts.[mvLogisticContract_id]
        ,mvLogisticContracts.[CONTRACTNUMBER] 
        ,mvLogisticContracts.[CUSTNMBR]
        ,mvLogisticContracts.[DateCreated]
        ,mvLogisticContracts.[TruckAllocatedTotal]      ,mvLogisticContracts.[mvLOgisticAddress_id_StartOriginal]
            ,mvLogisticContracts.[mvLogisticAddress_id_StartOriginal]       ,mvLogisticContractLineItems.[LineItemQuantity]     ,mvLogisticContractLineItems.[LineItemNo]
            ,mvLogisticContractLineItems.[OffloadRequired]
            ,mvLogisticContractLineItems.[CarrierRequested]     ,mvLogisticContractLineItems.[RequestedSerialNo]
            ,mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
            ,mvLogisticContractLineItems.[DateReadyBy]      ,mvLogisticContractLineItems.[DateCustomerRequested],
            mvLogisticContractLineItems.[mvLogisticContractLineItem_id]
        ,mvLogisticSalespersons.[FirstName],mvLogisticSalespersons.[LastName],
        mvLogisticServiceTypes.ServiceType,
        mvLogisticStatuses.Description, 
        mvLogisticSKUs.[SKU],       
        mvLogisticSKUs.[Length], 
        a1.CITY, a1.AddressName, a1.Address1, a1.STATE, a1.ZIP, 
        a2.CITY, a2.AddressName, a2.Address1, a2.STATE, a2.ZIP, 
        mvLogisticContracts.[mvLogisticAddress_id_Start], 
        mvLogisticContracts.[mvLogisticAddress_id_End]
  </code></pre>