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'
cbarber22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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.
0
cbarber22Author 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

0
PortletPaulfreelancerCommented:
Try changing:
SUM (TABLE2.LIPQOH) - (TABLE2.LIPCOM + TABLE2.LIHCOM)
to
SUM (TABLE2.LIPQOH - (TABLE2.LIPCOM + TABLE2.LIHCOM) )

I have also introduced ANSI joins below
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
      INNER JOIN PRODUCTIONDB.PRODDTA.TABLE2 TABLE2 ON TABLE3.MCMCU = TABLE2.LIMCU
      INNER JOIN PRODUCTIONDB.PRODDTA.TABLE1 TABLE1 ON TABLE2.LIITM = TABLE1.IMITM
      INNER JOIN PRODUCTIONDB.PRODDTA.TABLE5 TABLE5 ON TABLE1.IMLITM = TABLE5.JDLITM
      INNER JOIN PRODUCTIONDB.PRODDTA.TABLE4 TABLE4 ON TABLE5.JDLITM = TABLE4.COLITM
WHERE 1 = 1
ORDER BY
      'BUnit', 'ItemNumber'
;

Open in new window

ps: please use code tags for script. Let me know if you don't know what they are.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cbarber22Author Commented:
I just attempted this update, and it returned with the same results which has multiple values for a single itemnumber. Any other ideas?
0
PortletPaulfreelancerCommented:
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.
0
PortletPaulfreelancerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.