?
Solved

SUM the results of JOINed tables in MSSQL

Posted on 2009-05-06
9
Medium Priority
?
305 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
7 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 672 total points
ID: 24313092
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 664 total points
ID: 24313095
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
ID: 24313098
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 664 total points
ID: 24313110
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
ID: 24313134
0
 

Author Comment

by:rayluvs
ID: 24313185
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
ID: 24313297
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

850 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