?
Solved

sql join with sum

Posted on 2014-12-18
11
Medium Priority
?
112 Views
Last Modified: 2015-01-07
I am trying to join information from several tables.  
the main table (tblProject) has a project ID for every project... one of the other tables has a list volumes added for that project.  Each time you add to it another entry is made for that project ID.... I am trying to pull back a single line for each project ID that will include adding up the total (sum) of all the entrees from the other table.. here is what i have so far
the first section is just joining information from the three tables.. the second is my trying to figure out how to sum
thanks for the help
(without sum)
select tblProject.KEYField,tblProject.ProjectID,tblProject_Type.Name as 'Project Type',tblProject.Name as 'Project Name',tblTract1.Name as 'Primary Tract', tblProject.Status, tblProject.Acres, tblProject.Est_TOTAL as 'Est Vol'
from tblproject
inner join tblProject_Type
on tblProject.FKEYProjectType=tblProject_Type.KEYField
inner join tblTract1
on tblproject.Primary_Tract=tblTract1.KEYField

Open in new window

now with sum
select tblProject.KEYField,tblProject.ProjectID,tblProject_Type.Name as 'Project Type',tblProject.Name as 'Project Name',tblTract1.Name as 'Primary Tract', tblProject.Status, tblProject.Acres, tblProject.Est_TOTAL as 'Est Vol',sum(tblProject_Volume.Volume)as 'Sum'
from tblproject
inner join tblProject_Type
on tblProject.FKEYProjectType=tblProject_Type.KEYField
inner join tblTract1
on tblproject.Primary_Tract=tblTract1.KEYField
inner join tblProject_Volume
on tblProject.ProjectID=tblProject_Volume.FKEYProject
group by
tblProject.KEYField

Open in new window

0
Comment
Question by:David Modugno
[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
  • 6
  • 5
11 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40508337
You need to move the sum down into a subquery, like so:

select p.KEYField,p.ProjectID,pt.Name as 'Project Type',p.Name as 'Project Name',
    t.Name as 'Primary Tract', p.Status, p.Acres, p.Est_TOTAL as 'Est Vol', pv.Volume as 'Sum'
from tblproject p
inner join tblProject_Type pt
on p.FKEYProjectType=pt.KEYField
inner join tblTract1 t
on p.Primary_Tract=t.KEYField
left outer join (
    select FKEYProject, SUM(Volume) AS Volume
    from tblProject_Volume
    group by FKEYProject
) AS pv
on p.ProjectID=pv.FKEYProject
group by
p.KEYField
0
 

Author Comment

by:David Modugno
ID: 40509318
I get this error when i run it
Column 'tblproject.ProjectID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40509452
That string doesn't appear in my query anywhere.  Even if you converted my aliases back into full names, I don't see how that error would occur in my code.

Please post the text of the query, since otherwise it's almost impossible for me to debug.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:David Modugno
ID: 40509505
I copied your query directly.. here is a screen shot of the error with the query
sql-query.jpg
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40509538
D'OH, I'm so sorry, I forgot to remove the GROUP BY from the main query:

select p.KEYField,p.ProjectID,pt.Name as 'Project Type',p.Name as 'Project Name',
     t.Name as 'Primary Tract', p.Status, p.Acres, p.Est_TOTAL as 'Est Vol', pv.Volume as 'Sum'
from tblproject p
 inner join tblProject_Type pt
 on p.FKEYProjectType=pt.KEYField
 inner join tblTract1 t
 on p.Primary_Tract=t.KEYField
left outer join (
     select FKEYProject, SUM(Volume) AS Volume
     from tblProject_Volume
     group by FKEYProject
 ) AS pv
 on p.ProjectID=pv.FKEYProject
--group by /* I should have deleted the GROUP BY, DOH! */
-- p.KEYField
0
 

Author Comment

by:David Modugno
ID: 40509575
not i get this - little confused by this .... is it relating to the sum... that is an "int"
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value '0001-10' to data type int.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40509584
That's likely caused by one of the joins.  (At least) one of those joined columns must be nvarchar and not int.  If garbage can get into a column, it will get into a column :-).

Review the data type of "Volume" and all columns involved in the table joins.
0
 

Author Comment

by:David Modugno
ID: 40509616
I looked at the joins....
p.FKEYProjectType=pt.KEYField = both int
p.Primary_Tract=t.KEYField = both int

that leaves the sum....
don't know if this will help at all... but if i was to just run the sum... this is the result i would expect to see next to the results for one of the project
select SUM(Volume)as Volume
from tblProject_Volume
where FKEYProject = 1434

Open in new window

returns 3187

the entire query that i'm working on should bring back one line for each project and the last column would be that sum from tblProject_Volume .... i hope im not making this more confusing

the volume column in that table is also an int

thanks for all your help on this
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40509870
What about pv.FKEYProject?
0
 

Author Comment

by:David Modugno
ID: 40509872
also int
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40509924
Check those tables for a column with:
a data type of nvarchar  and
a (max_length >= 7 or max_length = 1).

For example:

SELECT OBJECT_NAME(c.object_id) AS table_name, c.*
FROM sys.columns c
INNER JOIN sys.types t on
    t.system_type_id = c.system_type_id AND
    t.user_type_id = c.user_type_id
WHERE
    OBJECT_NAME(c.object_id) IN (' tblproject ', 'tblProject_Type', 'tblTract1', 'tblProject_Volume')  AND --other tables need added?
    t.name LIKE '%nchar%' AND
    (c.max_length = -1 or c.max_length >= 7)

Then see if any of those columns are referenced in the query.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

765 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