Solved

SUM the results of JOINed tables in MSSQL

Posted on 2009-05-06
9
292 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
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 166 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 166 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

895 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