Link to home
Start Free TrialLog in
Avatar of cbarber22
cbarber22

asked on

SQL SUM Query

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'
Avatar of PortletPaul
PortletPaul
Flag of Australia image

I think you will have to share more specific details if we are to help you.
This issue of sums being inaccurate often stems from the way the tables are joined - which isn't visible in the question.

Note: You do not have to simplify the query for us, in fact sometime this can make it harder and take longer to reach an answer ( & real table and field names are better than generic ones in my view).

In addition I should say that "sample data" and an "expected result" are excellent tools for conveying your needs.
Avatar of cbarber22
cbarber22

ASKER

Below is the query I am currently using. This query currently retrieves all the desired data, the only issue is that when trying to calculate a sum or count for an Item based on "SUM (TABLE2.LIPQOH) - (TABLE2.LIPCOM + TABLE2.LIHCOM) AS 'Quantity'. It errors stating the following, aggregate function error, or if I modify the query it will run but include multiple entries for each item instead of retrieving the sum for each item.

The goal for the following query is to count or sum all the items with the same itemnumber creating a total in the quantity field instead of creating multiple entries with the totals for each based on Business Unit.
SELECT TABLE1.IMLITM AS 'ItemNumber', 
		 TABLE1.IMDSC1 AS 'Description', 
		SUM (TABLE2.LIPQOH) - (TABLE2.LIPCOM + TABLE2.LIHCOM) AS 'Quantity',
		 ltrim(TABLE3.MCMCU) AS 'BUnit', 
		 TABLE3.MCDC AS 'BUnitName', 
		 (TABLE4.COUNCS/10000) AS 'Price' 
	 FROM PRODUCTIONDB.PRODDTA.TABLE3 TABLE3, 
		 PRODUCTIONDB.PRODDTA.TABLE1 TABLE1, 
		 PRODUCTIONDB.PRODDTA.TABLE2 TABLE2, 
		 PRODUCTIONDB.PRODDTA.TABLE5 TABLE5, 
		 PRODUCTIONDB.PRODDTA.TABLE4 TABLE4 
	 WHERE TABLE1.IMITM = TABLE2.LIITM 
		 AND TABLE2.LIMCU = TABLE3.MCMCU 
		 AND TABLE1.IMLITM = TABLE5.JDLITM 
		 AND TABLE4.COLITM = TABLE5.JDLITM 
	ORDER BY 'BUnit', 'ItemNumber'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I just attempted this update, and it returned with the same results which has multiple values for a single itemnumber. Any other ideas?
Yes plenty, but they all start with: Sharing what you see with me.
Imagine if I asked you to review some code, and supplied no output - would you know if it worked or not?

The fastest, most effective, most widely proven approach is:
a. provide "sample data", and
b. an "expected result"

The sample data should be "per table" (ideally the table DDL and insert statements) but can be parsable text
Often Excel is used for these purposes.
Keep in mind the sample does NOT need to be extensive, often just a few rows will be sufficient.
just adding to the above, in this particular question, even just a sample of the current output, and a reworking of that to what you want to see, may be sufficient. Right now I'm flying blind and guessing what you want.