jeudi 23 juin 2016

How to Summarize all records with unique Material / plant codes into a single record based on Material

I have a single table in SQL Server with Material, Plant combinations with inventory qty's and values. This is a staging table for data coming in form the source system. I need to aggregate all these records with the same Material number but different plants into a single record based on Material Number with averaged qty's and values.

This is the Table I am working with.

enter image description here

I have tried many different SQL statements with this one being the closest to working. Note, The SQL prompt on material in the Where clause is there just to allow me to look at different Materials without copy pasting into the SQL statement.

SELECT
  material
 ,plant
 ,AVG(unrestricted) AS Unrestricted
 ,AVG(valueunrestricted) AS valUnrestricted
 ,SUM(ValueUnrestricted / unrestricted) AS UnitValue
FROM
  IBSCM01.stage.MB52
WHERE
  unrestricted IS NOT NULL AND
  unrestricted > 0 AND
  material = :material
GROUP BY
  material
 ,plant
 ,unrestricted
 ,valueunrestricted;

This is the output

enter image description here

What I am looking for is to output a single record based on Material that was aggregated across all plants. If just Group by Material it throws the following error.

Column 'IBSCM01.stage.MB52.Plant' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Aucun commentaire:

Enregistrer un commentaire