Solved

SUM the results of JOINed tables in MSSQL

Posted on 2009-05-06
9
290 Views
Last Modified: 2012-05-06
I would like to SUM up the resulting "Difference" of a SELECT I am using which has JOIN tables in it.  When I sum the "Diff" column I get error:

Column 'ces00rp02.ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.  I tried using GROUP By and also had error.

Here's the data and the SELECT script used to print\ it:

id          poprctnm          unitcost              cost                  Diff                    
----------- ----------------- --------------------- --------------------- -----------------------
1           RCT1200           3.29000               3.9900                -.70000
2           RCT1200           28.46000              29.4500               -.99000
3           RCT1200           2000.00000            2099.9900             -99.99000

SELECT ces00rp02.id, POP10310.poprctnm, POP10310.unitcost,ces00rp02.cost
FROM POP10310
JOIN ces00rp02
ON POP10310.poprctnm=ces00rp02.receiptno collate SQL_Latin1_General_CP1_CI_AS
and POP10310.rcptlnnm=ces00rp02.linenumber
WHERE  POP10310.poprctnm='RCT1200'

When i add to the end of the SELECT script the SUM part:

sum (POP10310.unitcost-ces00rp02.cost)

 I get the error.
0
Comment
Question by:rayluvs
9 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 168 total points
Comment Utility
SELECT ces00rp02.id, POP10310.poprctnm, POP10310.unitcost,ces00rp02.cost, sum (POP10310.unitcost-ces00rp02.cost) as diff
FROM POP10310
JOIN ces00rp02
ON POP10310.poprctnm=ces00rp02.receiptno collate SQL_Latin1_General_CP1_CI_AS
and POP10310.rcptlnnm=ces00rp02.linenumber
WHERE  POP10310.poprctnm='RCT1200'
group by ces00rp02.id, POP10310.poprctnm, POP10310.unitcost,ces00rp02.cost
0
 
LVL 46

Assisted Solution

by:tbsgadi
tbsgadi earned 166 total points
Comment Utility
Hi Ramante,

Try
SELECT     ces00rp02.id, POP10310.poprctnm, POP10310.unitcost, ces00rp02.cost, SUM(POP10310.unitcost - ces00rp02.cost) AS sumcost
FROM         dbo.POP10310 INNER JOIN
                      dbo.ces00rp02 ON POP10310.poprctnm = ces00rp02.receiptno COLLATE SQL_Latin1_General_CP1_CI_AS AND POP10310.rcptlnnm = ces00rp02.linenumber
GROUP BY ces00rp02.id, POP10310.poprctnm, POP10310.unitcost, ces00rp02.cost
HAVING      (POP10310.poprctnm = 'RCT1200')


Good Luck!

Gary
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
If you want the Sum of all then you need to try this

SELECT sum (POP10310.unitcost-ces00rp02.cost)
FROM POP10310
JOIN ces00rp02
ON POP10310.poprctnm=ces00rp02.receiptno collate SQL_Latin1_General_CP1_CI_AS
and POP10310.rcptlnnm=ces00rp02.linenumber
WHERE  POP10310.poprctnm='RCT1200'
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 166 total points
Comment Utility
SELECT     ces00rp02.id, POP10310.poprctnm, POP10310.unitcost, ces00rp02.cost, SUM(POP10310.unitcost - ces00rp02.cost) AS sumcost
FROM         dbo.POP10310 INNER JOIN
                      dbo.ces00rp02 ON POP10310.poprctnm = ces00rp02.receiptno COLLATE SQL_Latin1_General_CP1_CI_AS AND POP10310.rcptlnnm = ces00rp02.linenumber
GROUP BY ces00rp02.id, POP10310.poprctnm, POP10310.unitcost, ces00rp02.cost
HAVING      (POP10310.poprctnm = 'RCT1200')

will give you same result as you are getting now
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
0
 

Author Comment

by:rayluvs
Comment Utility
Worked Excellent!

But I would greatly appreciate it if I understood it fully.

So in order to SUM or COUNT, etc, I have to use GROUP by and have all the fields in the SELECT included in the GROUP by?

I understand the GROUP by the POP10310.poprctnm (because that an actual document number), but why GROUP by ces00rp02.id, POP10310.unitcost and ces00rp02.cost since these are values specific to the document?

Thanx
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
ces00rp02.id, POP10310.unitcost and ces00rp02.cost
means it group all the records where ces00rp02.id, POP10310.unitcost and ces00rp02.cost fields having same values and Sum that recors values

For more information refer
http://www.cetonline.iitkgp.ernet.in/csrg/data/resources/w3schools/www.w3schools.com/sql/sql_groupby.html
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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

16 Experts available now in Live!

Get 1:1 Help Now