Sum records

• legeboka

  Mr or Mrs. 500

  Points: 558

  More actions

  April 1, 2016 at 1:02 pm

  #312899

  Hi guys,

  I’m trying to write a query that will summarize cost and price by invoice and sequence number. I have details table and I need to roll details into 1 summary record. I simply can’t sum it because units purchased and units sold must be calculated separatly See example below. Your help is greatly appreciated. I have SQL 2014

  IF (SELECT OBJECT_ID('tempdb..#TempDetails'))is not null

  DROP TABLE #TempDetails

  CREATE TABLE #TempDetails

  (

  PK BIGINT, loadORdelivery VARCHAR(5),InvoiceNumber BIGINT, Units INT, SystemCost NUMERIC(18,4), SystemPrice NUMERIC(18,4),idp_serial INT

  )

  INSERT INTO #TempDetails

  SELECT 1,'L',1000, 10, 10.01,0, 50 UNION ALL

  SELECT 2,'D',1000, 11, 0,12.02, 50 UNION ALL

  SELECT 3,'L',1000, 20, 7.23,0, 51 UNION ALL

  SELECT 4,'D',1000, 21, 0,6.42, 51 UNION ALL

  SELECT 5,'L',1001,10, 1.01,0, 52 UNION ALL

  SELECT 6,'D',1001,11, 0,1.09, 52 UNION ALL

  SELECT 7,'L',1001,12, 3.33,0, 53 UNION ALL

  SELECT 8,'D',1001,13, 0,3.10, 53 UNION ALL

  SELECT 9,'L',1001,30, 4.00,0, 54 UNION ALL

  SELECT 10,'D',1001,31, 0,4.42, 54

  SELECT * from #TempDetails

  --Result expected

  select 'D' AS 'loadORdelivery', 1000 AS InvoiceNumber, 30 AS 'UnitsPurch', 32 AS 'UnitsSold', 17.24 AS 'SystemCost', 18.44 AS 'SystemPrice'

  UNION ALL

  select 'D' AS 'loadORdelivery', 1001 AS InvoiceNumber, 52 AS 'UnitsPurch', 55 AS 'UnitsSold', 8.34 AS 'SystemCost', 8.61 AS 'SystemPrice'

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  April 1, 2016 at 1:26 pm

  #1869258

  I think this is what you're looking for, but I had to make some assumptions:

  SELECT loadORdelivery='D', --Not sure what the point of returning a constant 'D' here is?

  InvoiceNumber,

  UnitsPurch= SUM(CASE WHEN loadORdelivery='L' THEN Units END),

  UnitsSold= SUM(CASE WHEN loadORdelivery='D' THEN Units END),

  SystemCost= SUM(SystemCost),

  SystemPrice=SUM(SystemPrice)

  FROM #TempDetails

  GROUP BY InvoiceNumber;

  I had to assume that an 'L' meant Units counted towards purchased units and a 'D' towards sold units, but that seemed pretty clear.

  I'm also a bit unsure what the point of returning the constant 'D' in the aggregated results is for, but sometimes there are strange requirements. 🙂

  Cheers!

• legeboka

  Mr or Mrs. 500

  Points: 558

  More actions

  April 1, 2016 at 1:47 pm

  #1869260

  It will work.

  Thank you