[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SUM Query

Posted on 2014-08-18
6
Medium Priority
?
426 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'
0
Comment
Question by:cbarber22
  • 4
  • 2
6 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40269269
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
 

Author Comment

by:cbarber22
ID: 40269319
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40269347
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:cbarber22
ID: 40269387
I just attempted this update, and it returned with the same results which has multiple values for a single itemnumber. Any other ideas?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40269393
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40269398
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question