We help IT Professionals succeed at work.

SQL SUM Query

459 Views
Last Modified: 2014-08-18
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'
Comment
Watch Question

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

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

Author

Commented:
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

EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I just attempted this update, and it returned with the same results which has multiple values for a single itemnumber. Any other ideas?
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.