I have been tasked with modifying an existing sql query that has been pulling inaccurate data for several years now that was never noticed. The query has now been modified to pull accurate data for our inventory but because we have multiple facilities, it does not count the item in total, it creates a separate entry for each facility which it resides in. I am looking to leave this as is for one query, and duplicate this query to create a master sum for each facility with totals of each item no matter the location. I have tried, both the sum, and count functions but both are still creating multiple entries for the same ItemNumber which then obviously does not create a grand total of the 1 item it shows as multiple rows in the results of the query.
SELECT Field1 AS 'ItemNumber',
Field2 AS 'Description',
SUM((Field3) - (Field4 + Field5)) AS 'Quantity',
Field6 AS 'Facility'