David Modugno
asked on
sql join with sum
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)
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
now with sumselect 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Please post the text of the query, since otherwise it's almost impossible for me to debug.
ASKER
I copied your query directly.. here is a screen shot of the error with the query
sql-query.jpg
sql-query.jpg
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.KEYFi eld
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
select p.KEYField,p.ProjectID,pt.
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.KEYFi
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
ASKER
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.
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value '0001-10' to data type int.
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.
Review the data type of "Volume" and all columns involved in the table joins.
ASKER
I looked at the joins....
p.FKEYProjectType=pt.KEYFi eld = 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
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
p.FKEYProjectType=pt.KEYFi
p.Primary_Tract=t.KEYField
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
returns 3187the 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
What about pv.FKEYProject?
ASKER
also int
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.
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.
ASKER
Column 'tblproject.ProjectID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause