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.
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
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