combining two fields with a group by RSS

• • Reply
  

  eihn

  Member

  56 Points

  125 Posts

  combining two fields with a group by

  Oct 19, 2010 06:19 PM|LINK

  declare @jax table
  (
  cus nvarchar(50),
  sum int,
  sumly int,
  rep nvarchar(50)
  )
  
  
  insert into @jax (rep,cus,sum)
  SELECT     TOP 10 dbo.tblTerritory.fldSalesRep,dbo.ExecSales.CUSTOMER_NAME,
                        SUM(dbo.ExecSales.JAN_SLS_AMNT + dbo.ExecSales.FEB_SLS_AMNT + dbo.ExecSales.MAR_SLS_AMNT + dbo.ExecSales.APR_SLS_AMNT + dbo.ExecSales.MAY_SLS_AMNT
                         + dbo.ExecSales.JUN_SLS_AMNT + dbo.ExecSales.JUL_SLS_AMNT + dbo.ExecSales.AUG_SLS_AMNT + dbo.ExecSales.SEP_SLS_AMNT + dbo.ExecSales.OCT_SLS_AMNT
                         + dbo.ExecSales.NOV_SLS_AMNT + dbo.ExecSales.DEC_SLS_AMNT) AS sum
  FROM         dbo.tblTerritory INNER JOIN
                        dbo.ExecSales ON dbo.tblTerritory.fldBranch = dbo.ExecSales.BRANCHID
  WHERE     (dbo.tblTerritory.fldSalesRep = N'mbradshaw') AND (dbo.ExecSales.load_year = 2010)
  GROUP BY dbo.ExecSales.load_year, dbo.tblTerritory.fldSalesRep, dbo.ExecSales.BRANCHID, dbo.ExecSales.CUSTOMER_NAME
  ORDER BY sum DESC
  
  
  
  
  insert into @jax (rep,sumly)
  SELECT     TOP 10  dbo.tblTerritory.fldSalesRep,SUM(dbo.ExecSales.JAN_SLS_AMNT + dbo.ExecSales.FEB_SLS_AMNT + dbo.ExecSales.MAR_SLS_AMNT + dbo.ExecSales.APR_SLS_AMNT + dbo.ExecSales.MAY_SLS_AMNT
                         + dbo.ExecSales.JUN_SLS_AMNT + dbo.ExecSales.JUL_SLS_AMNT + dbo.ExecSales.AUG_SLS_AMNT + dbo.ExecSales.SEP_SLS_AMNT + dbo.ExecSales.OCT_SLS_AMNT
                         + dbo.ExecSales.NOV_SLS_AMNT + dbo.ExecSales.DEC_SLS_AMNT) AS sum
  FROM         dbo.tblTerritory INNER JOIN
                        dbo.ExecSales ON dbo.tblTerritory.fldBranch = dbo.ExecSales.BRANCHID
  WHERE     (dbo.tblTerritory.fldSalesRep = N'mbradshaw') AND (dbo.ExecSales.load_year = 2009)
  GROUP BY dbo.ExecSales.load_year, dbo.tblTerritory.fldSalesRep, dbo.ExecSales.BRANCHID, dbo.ExecSales.CUSTOMER_NAME
  ORDER BY sum DESC
  
  
  select * from @jax

  
   

  which is giving me

   

  

   

  And i tried

  select * from @jax

  group by rep

   

  and it did not group them. How would i bring the sum ly up with the sum? so it shows 1-10  cust,sum,sumly

   

  THanks all who help

   

   

• • Reply
  

  limno

  All-Star

  117326 Points

  8003 Posts

  Moderator

  MVP

  Re: combining two fields with a group by

  Oct 19, 2010 06:39 PM|LINK

  insert into @jax (rep,cus,sum, sumly)
  
  
  
  
  SELECT     TOP 10 dbo.tblTerritory.fldSalesRep,dbo.ExecSales.CUSTOMER_NAME
                       ,CASE WHEN dbo.ExecSales.load_year = 2010 THEN SUM(dbo.ExecSales.JAN_SLS_AMNT + dbo.ExecSales.FEB_SLS_AMNT + dbo.ExecSales.MAR_SLS_AMNT + dbo.ExecSales.APR_SLS_AMNT + dbo.ExecSales.MAY_SLS_AMNT
                         + dbo.ExecSales.JUN_SLS_AMNT + dbo.ExecSales.JUL_SLS_AMNT + dbo.ExecSales.AUG_SLS_AMNT + dbo.ExecSales.SEP_SLS_AMNT + dbo.ExecSales.OCT_SLS_AMNT
                         + dbo.ExecSales.NOV_SLS_AMNT + dbo.ExecSales.DEC_SLS_AMNT) END AS sum
                         , CASE WHEN dbo.ExecSales.load_year = 2009 THEN SUM(dbo.ExecSales.JAN_SLS_AMNT + dbo.ExecSales.FEB_SLS_AMNT + dbo.ExecSales.MAR_SLS_AMNT + dbo.ExecSales.APR_SLS_AMNT + dbo.ExecSales.MAY_SLS_AMNT
                         + dbo.ExecSales.JUN_SLS_AMNT + dbo.ExecSales.JUL_SLS_AMNT + dbo.ExecSales.AUG_SLS_AMNT + dbo.ExecSales.SEP_SLS_AMNT + dbo.ExecSales.OCT_SLS_AMNT
                         + dbo.ExecSales.NOV_SLS_AMNT + dbo.ExecSales.DEC_SLS_AMNT) END AS sumly
  FROM         dbo.tblTerritory INNER JOIN
                        dbo.ExecSales ON dbo.tblTerritory.fldBranch = dbo.ExecSales.BRANCHID
  WHERE     (dbo.tblTerritory.fldSalesRep = N'mbradshaw') AND (dbo.ExecSales.load_year = 2010 OR dbo.ExecSales.load_year = 2009)
  GROUP BY dbo.ExecSales.load_year, dbo.tblTerritory.fldSalesRep, dbo.ExecSales.BRANCHID, dbo.ExecSales.CUSTOMER_NAME
  ORDER BY sum DESC

  
   

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by eihn on Oct 19, 2010 07:02 PM