Solved

SUM the results of JOINed tables in MSSQL

Posted on 2009-05-06
9
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

690 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