SUM the results of JOINed tables in MSSQL

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.
rayluvsAsked:
Who is Participating?
 
knightEknightCommented:
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
 
tbsgadiCommented:
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
 
Pratima PharandeCommented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Pratima PharandeCommented:
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
 
rayluvsAuthor Commented:
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
 
Pratima PharandeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.