Solved

Sum not working in sql query

Posted on 2014-10-07
13
171 Views
Last Modified: 2014-10-08
So when I run the following query in sql server:

SELECT     dbo.Invoice_Detail.Packlist, dbo.Invoice_Detail.Customer_PO, dbo.Job.Customer, SUM(dbo.Invoice_Detail.Amount) AS invtotal, dbo.Invoice_Detail.[Document]
FROM         dbo.Invoice_Detail INNER JOIN
                      dbo.Job ON dbo.Invoice_Detail.Job = dbo.Job.Job INNER JOIN
                      dbo.Invoice_Header ON dbo.Invoice_Detail.[Document] = dbo.Invoice_Header.[Document]
GROUP BY dbo.Invoice_Detail.[Document], dbo.Invoice_Detail.Packlist, dbo.Invoice_Detail.Customer_PO, dbo.Job.Customer, dbo.Invoice_Header.Document_Date
HAVING      (dbo.Invoice_Header.Document_Date = CONVERT(DATETIME, '2014-10-06 00:00:00', 102))

Open in new window


I get the following results:

76169      20827      DIRTT      882.3100      20866
76171      20828      DIRTT      196.2400      20867
76170      20826      DIRTT      143.3400      20868
75246      GIATC000027      GLOBALINV      4217.6700      20869
76197      20823      DIRTT      130.4000      20870
76218      20824      DIRTT      274.5000      20871
76304      10107      PRIN001      5027.5000      20872
76057      10071      PRIN001      1068.2400      20873
76226      10071      PRIN001      2502.2400      20874
76054      10097      PRIN001      777.1400      20875
76255      10097      PRIN001      777.1400      20876
76257      214299      PRIN001      230.0000      20877
76238      57030234      YANCEY      253.1600      20879
76301      59325      HYEP001      5219.6000      20881
76010      97297      ENER001      470.5800      20882
76190      98293      ENER001      177.3000      20883
76173      214371      PRIN001      90.0000      20885
76240      PA021143-14      CLYDE001      695.6600      20886
76241      PA020689-14      CLYDE001      3647.5600      20887
76237      13-1184-028      YANCEY      277.3900      20888
76019      20586      DIRTT      2470.0000      20889
76089      4900      ELLI001      210.0000      20890

Here is the problem. The first record (document = 20866) has a total of 882.3100. This is 58.12 less than I was expecting. So I ran the following query:

SELECT Packlist, Customer_PO, Amount, [Document]
  FROM [ppp].[dbo].[Invoice_Detail]
  where Document = '20866'

Open in new window


and the following results where returned:

Packlist      Customer_PO      Amount      Document
76169      20827      18.63      20866
76169      20827      18.63      20866
76169      20827      18.63      20866
76169      20827      18.63      20866
76169      20827      37.26      20866
76169      20827      18.83      20866
76169      20827      18.83      20866
76169      20827      18.83      20866
76169      20827      18.83      20866
76169      20827      37.66      20866
76169      20827      53.78      20866
76169      20827      27.63      20866
76169      20827      86.28      20866
76169      20827      42.40      20866
76169      20827      22.66      20866
76169      20827      70.41      20866
76169      20827      31.78      20866
76169      20827      31.78      20866
76169      20827      31.78      20866
76169      20827      31.78      20866
76169      20827      63.56      20866
76169      20827      50.62      20866
76169      20827      27.62      20866
76169      20827      85.47      20866
NULL      NULL      58.12      20866

Notice that the last line is 58.12. This actually a shipping cost that is added. There is nothing I can do about the nulls. Is there any way to adjust the first query to give me the proper sum?

Thanks in advance
0
Comment
Question by:princeservice
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>The first record (document = 20866) has a total of 882.3100. This is 58.12 less than I was expecting.  
>NULL      NULL      58.12      20866

The first query groups on Packlist and Customer_PO, which I'm guessing are the first two columns, which means that this amount  would be in the result set somewhere down in the bottom with both NULL values.

NULL does not equal 76169, or anything else, so by design those rows are not grouped with the 76169's.

>There is nothing I can do about the nulls.
Okay, but since NULL does not equal 76169 (or any other value ), the only way to pull this off would be to GROUP BY Document, and none of the other columns, i.e.
SELECT [Document], SUM(Amount) as amount_sum
FROM [ppp].[dbo].[Invoice_Detail]
where Document = '20866'

Open in new window

What you could do is use the second query returning the correct amounts as a subquery, where you create a query similar to the first one, JOIN (the second query as a subquery) ON first_query.Document = second_query.Document
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

Try this

SELECT     dbo.Invoice_Detail.Packlist, dbo.Invoice_Detail.Customer_PO, dbo.Job.Customer, SUM(dbo.Invoice_Detail.Amount) AS invtotal, dbo.Invoice_Detail.[Document]
FROM         dbo.Invoice_Detail INNER JOIN
                      dbo.Job ON dbo.Invoice_Detail.Job = dbo.Job.Job LEFT OUTER JOIN
                      dbo.Invoice_Header ON dbo.Invoice_Detail.[Document] = dbo.Invoice_Header.[Document]
GROUP BY dbo.Invoice_Detail.[Document], dbo.Invoice_Detail.Packlist, dbo.Invoice_Detail.Customer_PO, dbo.Job.Customer, dbo.Invoice_Header.Document_Date
HAVING      (dbo.Invoice_Header.Document_Date = CONVERT(DATETIME, '2014-10-06 00:00:00', 102))

Open in new window


By using LEFT OUTER JOIN with the Invoice_Header you will most likely get the missing line with the nulls in the Invoice_Detail.

Regards,
      Tomas Helgi
0
 

Author Comment

by:princeservice
Comment Utility
TomasHelgi - I got the same results

Jim Horn -

"NULL does not equal 76169, or anything else, so by design those rows are not grouped with the 76169's"

I really don't want to group by packlist (76169). I want to group by document (20866) which is not null. I don't know how to avoid SQL forcing me to add "group by" to every column. I need Customer PO and packlist to be returned in the results.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
I cleaned up your code somewhat.  Give this a whirl.
SELECT id.[Document] SUM(id.Amount) AS invtotal
FROM dbo.Invoice_Detail id
   JOIN dbo.Job j ON id.Job = j.Job 
   JOIN dbo.Invoice_Header ih ON id.[Document] = ih.[Document]
WHERE ih.Document_Date = '2014-10-06'
GROUP BY id.[Document]

Open in new window

1.

Table aliases id, j, and ih are used to make the code much more readable.

2.

HAVING is for filtering on aggregates, such as 'total amount greater than $1 million', so if you're filtering on a single value that belongs in the WHERE clause.

3.

What data type is ih.Document_Date?  If it's just a date, then use value '2014-10-06'.  If it's datetime, you can still use just the date, as all dates are assumed to have a time component of 00:00:00.  Using the CONVERT will only come into play if you need the date to be a certain cosmetic format, and select/compare it as a character value.

4.

I removed all the columns in the SELECT and GROUP BY except for document and amount

5.

JOIN is the same as INNER JOIN
0
 

Author Comment

by:princeservice
Comment Utility
Jim Horn - When I copy and paste I get:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'id'.

But besides that, I am missing two very important columns - Customer PO and packlist.
0
 

Author Comment

by:princeservice
Comment Utility
Lets start over because I think there is some confusion. Pretend I have a table like this:

Packlist      Customer_PO      Amount      Document
76169      20827      18.63      20866
76169      20827      18.63      20866
76169      20827      18.63      20866
76169      20827      18.63      20866
76169      20827      37.26      20866
76169      20827      18.83      20866
76169      20827      18.83      20866
76169      20827      18.83      20866
76169      20827      18.83      20866
76169      20827      37.66      20866
76169      20827      53.78      20866
76169      20827      27.63      20866
76169      20827      86.28      20866
76169      20827      42.40      20866
76169      20827      22.66      20866
76169      20827      70.41      20866
76169      20827      31.78      20866
76169      20827      31.78      20866
76169      20827      31.78      20866
76169      20827      31.78      20866
76169      20827      63.56      20866
76169      20827      50.62      20866
76169      20827      27.62      20866
76169      20827      85.47      20866
NULL      NULL      58.12      20866

I want to create a query that returns this:

Packlist      Customer_PO      Amount      Document
76169      20827      940.4300      20866

I need the amount column to be the sum of every row, even the Customer PO and packlist rows that have null in the last row.

Remember this - the document number will never be null and the packlist and customer po will almost always have a single line that has nulls.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
>But besides that, I am missing two very important columns - Customer PO and packlist.
Hence the problem.  You can't include those columns, and expect the Sum(amount) to match, as 76169 and 20827 are not the same as NULL and NULL, so they won't group together.

One possibility is to use a subquery, like this...  <air code>
SELECT id.Packlist, id.Customer_PO, j.Customer, id.Document, details.InvTotal
FROM dbo.Invoice_Detail id
   JOIN dbo.Job j ON id.Job = j.Job 
   JOIN dbo.Invoice_Header ih ON id.[Document] = ih.[Document]
   JOIN (
      SELECT Document SUM(id.Amount) AS invtotal
      FROM dbo.Invoice_Detail id
	  GROUP BY Document) details ON id.Document = details.Document
WHERE ih.Document_Date = '2014-10-06'
GROUP BY id.Packlist, id.Customer_PO, j.Customer, id.Document

Open in new window


If this isn't possible, then you're ultimately going to have to answer the question of How does SQL interpret NULL and NULL to be grouped with 76169 and 20827 ?
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Based on the simplified single table above, copy-paste the below to your SSMS, test it to verify it works, and then apply it to your situation:
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
GO

CREATE TABLE #tmp (packlist int, customer_po int, amount money, document int) 
GO

INSERT INTO #tmp (packlist, customer_po, amount, document)
VALUES 
   (76169, 20827, 18.63, 20866), 
   (76169, 20827, 18.63, 20866), 
   (76169, 20827, 18.63, 20866), 
   (NULL, NULL, 58.12, 20866) 

SELECT t_values.packlist, t_values.customer_po, t.document, SUM(t_sum.sum_amount) as amount
FROM #tmp t
   JOIN (SELECT document, SUM(amount) as sum_amount
         FROM #tmp
         GROUP BY document) t_sum ON t.document = t_sum.document
   JOIN (SELECT document, packlist, customer_po
         FROM #tmp
         WHERE packlist IS NOT NULL) t_values ON t.document = t_sum.document
GROUP BY t_values.packlist, t_values.customer_po, t.document

Open in new window

0
 

Author Comment

by:princeservice
Comment Utility
Jim Horn -
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
If you click on the error, which line does the cursor jump to?
The line 6 in your SSMS might not be the same as the line 6 in the code block, and I don't see any () errors in my code.
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

Try this.

SELECT X.DOCUMENT, SUM(X.invtotal) INVTOTAL
FROM (
SELECT     dbo.Invoice_Detail.Packlist, dbo.Invoice_Detail.Customer_PO, dbo.Job.Customer, SUM(dbo.Invoice_Detail.Amount) AS invtotal, dbo.Invoice_Detail.[Document]
FROM         dbo.Invoice_Detail LEFT OUTER JOIN
                      dbo.Job ON dbo.Invoice_Detail.Job = dbo.Job.Job LEFT OUTER JOIN
                      dbo.Invoice_Header ON dbo.Invoice_Detail.[Document] = dbo.Invoice_Header.[Document]
GROUP BY dbo.Invoice_Detail.[Document], dbo.Invoice_Detail.Packlist, dbo.Invoice_Detail.Customer_PO, dbo.Job.Customer, dbo.Invoice_Header.Document_Date
HAVING      (dbo.Invoice_Header.Document_Date = CONVERT(DATETIME, '2014-10-06 00:00:00', 102))
)X 
GROUP BY X.DOCUMENT

Open in new window


Regards,
    Tomas Helgi
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
SELECT     MAX(id.Packlist) AS Packlist, MAX(id.Customer_PO) AS Customer_PO, MAX(j.Customer) AS Customer,
                  SUM(id.Amount) AS invtotal, id.[Document]
FROM         dbo.Invoice_Detail id INNER JOIN
                      dbo.Job j ON id.Job = j.Job INNER JOIN
                      dbo.Invoice_Header ih ON id.[Document] = ih.[Document]
WHERE       (ih.Document_Date = '20141006 00:00:00')
GROUP BY id.[Document]
ORDER BY [Document]
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
As long as there is only one packlist and customer_po to each document (ignoring the NULLs) then you can safely use Scott's suggestion.

Does this return any records?
SELECT
      [Document]
FROM [ppp].[dbo].[Invoice_Detail]
GROUP BY
      [Document]
HAVING
      COUNT(DISTINCT Packlist) > 1
   OR COUNT(DISTINCT Customer_PO) > 1
;

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now