Link to home
Start Free TrialLog in
Avatar of David Modugno
David Modugno

asked on

Problem with sql query

I have a sql Query with Joins.. I need to join information from another table but in this case I need to sum a total
Here is my existing query
Select tblProject.KEYField, tblProject.Project_Code, tblProject.GL_Code,tblProject.Plan_Year,tblProject_Type.Name, tblProject.Name, Acres, Est_Total, c_Total, tblEmployee.Last_Name  + ', ' + tblEmployee.First_Name as FullName, Load_Start_Date, Load_End_Date, tblProject.Evac_Form_Authorized,tblProject.Is_Complete
From tblProject
Join tblProject_Type
On tblProject.FKEYProjectType = tblProject_Type.KeyField
Join tblEmployee
On tblProject.Project_Owner = tblEmployee.KeyField            
Order by tblProject.Name

Open in new window


the new table looks like this (table is called tblProject_Volume)
KEYField, FKeyProject (this is the project number from the main project table), Volume
in that table you will find several entries of Volume for each project

I need to know how to add sum(volume) to the query above
Results would show 1 line for each project and the last field would be the sum of all the volums for the that project from the tblProject_Volume table

I hope that makes since
Thanks in advance experts
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
Assuming that "the project number from the main project table" is also called FKeyProject then something like this should do it.

SELECT  p.KEYField,
        p.Project_Code,
        p.GL_Code,
        p.Plan_Year,
        t.Name,
        p.Name,
        Acres,
        Est_Total,
        c_Total,
        e.Last_Name + ', ' + e.First_Name AS FullName,
        Load_Start_Date,
        Load_End_Date,
        p.Evac_Form_Authorized,
        p.Is_Complete,
	v.TotalVolume
FROM    tblProject p
        JOIN tblProject_Type t ON p.FKEYProjectType = t.KeyField
        JOIN tblEmployee e ON p.Project_Owner = e.KeyField
		JOIN (
			SELECT	KEYField, FKeyProject, SUM(Volume) TotalVolume
			FROM	tblProject_Volume
			GROUP BY
				KeYField, FKeyProject) v ON p.FKeyProject = v.FKeyProject and p.KEYField = v.KEYField
ORDER BY p.Name;

Open in new window

Avatar of David Modugno
David Modugno

ASKER

thanks for the answer.. I get an error when I run it

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v.FKeyProject" could not be bound.
Msg 207, Level 16, State 1, Line 23
Invalid column name 'FKeyProject'.
Avatar of David Modugno
David Modugno

ASKER

and yes  KEYField in the project table is FKEYProject in the tblProject_Volume table
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
I suggest you post the schema (CREATE TABLE) for all the tables involved and their relationships as clearly something is getting lost in translation.
Avatar of David Modugno
David Modugno

ASKER

The project table is a monster.... but here they are
tblProject_Volume
USE [TimberOps]
GO
/****** Object:  Table [dbo].[tblProject_Volume]    Script Date: 08/31/2015 09:51:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblProject_Volume](
	[KEYField] [int] IDENTITY(1,1) NOT NULL,
	[FKEYProject] [int] NOT NULL,
	[volDate] [datetime] NULL,
	[Volume] [int] NULL,
	[FKEYContractor] [int] NULL,
	[Date_Entered] [datetime] NULL CONSTRAINT [DF_tblProject_Volume_Date_Entered]  DEFAULT (getdate()),
	[FKEYVolume_Type] [int] NULL CONSTRAINT [DF_tblProject_Volume_FKEYVolume_Type]  DEFAULT ((1)),
 CONSTRAINT [PK_tblProject_Volume] PRIMARY KEY CLUSTERED 
(
	[KEYField] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1275 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'KEYField'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1665 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYProject'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1350 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'volDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYContractor'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2580 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'Date_Entered'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2340 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=Budget, 2 = Actual, 3 = ??? Cutting' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DisplayControl', @value=N'109' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Format', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_IMEMode', @value=N'0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume', @level2type=N'COLUMN',@level2name=N'FKEYVolume_Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_FilterOnLoad', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_HideNewField', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=N'[tblProject_Volume].[FKEYProject] DESC' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=True , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOnLoad', @value=True , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TotalsRow', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblProject_Volume'

Open in new window


tblProject .... attached file

thanks again for the help
tblProject.sql
Avatar of David Modugno
David Modugno

ASKER

any thoughts experts
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
I suggest you post the schema (CREATE TABLE) for all the tables involved and their relationships
You included the schema for two tables tblProject and tblProject_Volume with no mention of tblProject_Type or tblEmployee.  Even without these tables I could probable guess (I just would not be able to test) if I knew what was the relationship between these two tables (tblProject and blProject_Volume) .  Is it perhaps:
1. tblProject.KEYField = tblProject_Volume.FKEYProject
2. Something else?
Avatar of David Modugno
David Modugno

ASKER

I am no sql expert by any means... I grabbed where I would expect to see the relationships (see pic) but I don't see any...
It looks like when the tables were created they did not create them

but

From a use perspective
if I take a keyfield from tblProject  (418 as example is Daugherty 2) - see pic for the result
and run this query I get all volumes for that project number against the tblProject_Volume
SELECT     KEYField, FKEYProject, volDate, Volume, FKEYContractor, Date_Entered, FKEYVolume_Type
FROM         tblProject_Volume
WHERE     (FKEYProject = 418)

Open in new window


I need the sum of all the volume for that project in the other query

Sorry I don't know this stuff better.. I hop this makes since
tables.jpg
volumequery.jpg
Avatar of David Modugno
David Modugno

ASKER

just to double check... I ran this query (found it online) against both tblProject and tblProject_volume
and it returned no  relationships

select name 'ForeignKeyName', 
    OBJECT_NAME(referenced_object_id) 'RefrencedTable',
    OBJECT_NAME(parent_object_id) 'ParentTable'
from sys.foreign_keys
where referenced_object_id = OBJECT_ID('tblProject_volume') or 
    parent_object_id = OBJECT_ID('tblProject_volume')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Modugno
David Modugno

ASKER

Perfect Thank you
Avatar of David Modugno
David Modugno

ASKER

Thanks for all your help... worked perfectly