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,ces00rp0
FROM POP10310
JOIN ces00rp02
ON POP10310.poprctnm=ces00rp0
and POP10310.rcptlnnm=ces00rp0
WHERE POP10310.poprctnm='RCT1200
When i add to the end of the SELECT script the SUM part:
sum (POP10310.unitcost-ces00rp
I get the 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,ces00rp0
FROM POP10310
JOIN ces00rp02
ON POP10310.poprctnm=ces00rp0
and POP10310.rcptlnnm=ces00rp0
WHERE POP10310.poprctnm='RCT1200
When i add to the end of the SELECT script the SUM part:
sum (POP10310.unitcost-ces00rp
I get the error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
SELECT sum (POP10310.unitcost-ces00rp
FROM POP10310
JOIN ces00rp02
ON POP10310.poprctnm=ces00rp0
and POP10310.rcptlnnm=ces00rp0
WHERE POP10310.poprctnm='RCT1200