Select statement to return sum by year

• kwoznica

  SSCertifiable

  Points: 5275

  More actions

  November 9, 2013 at 1:58 pm

  #286127

  I am working on a query to show a sum of costs by year in a vendor purchase table. the final output I am trying to achieve will show the Vendor Number, Vendor Name, Year, Total Sum.

  Below is a sample table.

  CREATE TABLE [dbo].[tempVendorPurchases]

  (

  [VendorNum] [INT] NOT NULL,

  [Amount] [int] NOT NULL,

  [PostingDate] [Date] NOT NULL,

  [VendorName] [varchar](20) NOT NULL,

  )

  GO

  insert into [dbo].[tempVendorPurchases]

  (

  [VendorNum],[Amount], [PostingDate], [VendorName]

  )

  Values

  ( '1234', '100', '2012-01-15', 'SoftwareCo')

  ,( '1234', '10000', '2013-02-15', 'SoftwareCo')

  ,( '1234', '12500', '2011-02-15', 'SoftwareCo')

  ,( '1234', '15300', '2012-03-15', 'SoftwareCo')

  ,( '1234', '13450', '2011-08-15', 'SoftwareCo')

  ,( '1234', '10150', '2012-05-15', 'SoftwareCo')

  ,( '1234', '18000', '2013-06-15', 'SoftwareCo')

  Select * from [dbo].[tempVendorPurchases];

  The end result would look like the below set.

  Vendor Number Vendor Name Year TotalSum

  1234 SoftwareCo 2011 25950

  1234 SoftwareCo 2012 25550

  1234 SoftwareCo 2013 28000

  Any suggestions are welcome. Thanks.

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  November 9, 2013 at 2:07 pm

  #1665319

  I haven't tested this with your ssample data, but I think this will give you the result:

  SELECT

  [VendorNum]

  , [VendorName]

  , YEAR([PostingDate]) as 'Year'

  , Sum([Amount]) as 'TotalSum'

  FROM [tempVendorPurchases]

  GROUP BY

  [VendorNum]

  , [VendorName]

  , YEAR([PostingDate])

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• kwoznica

  SSCertifiable

  Points: 5275

  More actions

  November 14, 2013 at 11:15 am

  #1666480

  Just what I needed. Thanks HanShi!