April 21, 2009 at 9:26 am
Hello, I am doing a small query for a cost report. I am using 5 tables: TProject, THardware, TSoftware, TTravel and TMaterial. These tables: THardware, TSoftware, TTravel and TMaterial have the following attributes: month, cost and the ID of the project. What I need in the report is the cost for each project. So far this is what ive done, but is not working, I dont know why the cost totals are incorrect, I will appreciate if someone can help me.
SELECT p.name, SUM(s.cost) AS Expr1, SUM(m.cost) AS Expr2, SUM(t.cost) AS Expr3, SUM(h.cost) AS Expr4
FROM THardware AS h INNER JOIN
TProject AS p ON h.idProject = p.idProyect INNER JOIN
TSoftware AS s ON p.idProyect = s.idProject INNER JOIN
TTravel AS t ON p.idProyect = t.idProject INNER JOIN
TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
April 21, 2009 at 9:43 am
i suspect the issue is you used all INNER Joins...that means ther MUST be data in all 5 tables for every project....intuitively, for example, i doubt every project has travel expenses...
what happens if you switch to Left Outer Joins instead:
SELECT p.name, SUM(s.cost) AS Expr1, SUM(m.cost) AS Expr2, SUM(t.cost) AS Expr3, SUM(h.cost) AS Expr4
FROM THardware AS h
LEFT OUTER JOIN TProject AS p ON h.idProject = p.idProyect
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idProject
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
Lowell
April 21, 2009 at 9:52 am
Also I am guessing whether u have column with NULL values, in that case Aggregate functions will give u incorrect results. I fu do have isnull columns then u should replace those columns with 0. i.e
ISNULL(col1,0)
April 21, 2009 at 10:20 am
I tried with the left join but the SUM is still not correct, I check the expected result thousands of times but it still dont match. I also check and there are no NULL fields, all cost fields have a least a zero. I don't know if the fact that i am using decimal type for costs fields affects?
April 21, 2009 at 10:53 am
can you tell us why the numbers are "wrong"? grouping to get totals is pretty straight forward;
i don't think it makes a difference, but your first table is THardware, is there more than one Hardware per project? if this is a project report, i'd always make that my first table:
SELECT p.name, SUM(s.cost) AS Expr1, SUM(m.cost) AS Expr2, SUM(t.cost) AS Expr3, SUM(h.cost) AS Expr4
FROM TProject AS p
LEFT OUTER JOIN THardware AS h ON p.idProyect = h.idProject
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idProject
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON p.idProyect = m.idProject
GROUP BY p.name
Lowell
April 21, 2009 at 11:19 am
Hi, thanks for responding so quickly!
There is more than one hardware value for each project. For example suppose I have a project named TEST. In the hardware table there can be more tan one row associate to TEST project. The same for the rest of the tables.
projectName hardwareCost hardwareDesripcion
TEST 1000 server
TEST 1000 PC
Any clue about what can be wrong?
April 21, 2009 at 11:27 am
Your example below was what I expected;
project is at the top of the data pyramid, but The grouping on the query looks ok to me;
so....how is are the numbers wrong?
are the costs too high? too low? all zeros?
what were the results of either of the two queries I pasted, and how are they incorrect from the expected values, if you can explain it;
do any of the records need additional filtering, like costStatus='Approved' or something in order to be included in the costs?
moramoga (4/21/2009)
Hi, thanks for responding so quickly!There is more than one hardware value for each project. For example suppose I have a project named TEST. In the hardware table there can be more tan one row associate to TEST project. The same for the rest of the tables.
projectName hardwareCost hardwareDesripcion
TEST 1000 server
TEST 1000 PC
Any clue about what can be wrong?
Lowell
April 21, 2009 at 12:03 pm
Hi again, I did some test to check and this are my results:
1. I deleted all software, hardware, material and travel records.
2. I inserted 1000$ for project TEST in the hardware table, then I run the query and the results were fine.
3.Then I inserted another 1000 for project TEST, run the query and results were good.
4.Then I inserted another 10000 for project TEST, run the query and results were good.
5.Then I inserted 1000 but for SOFTWARE, run the query and the results were wrong.
The expected results were Software total 1000 and Hardware 12000
The real results were : Software 4000 and hardware 24000
April 21, 2009 at 1:49 pm
Please post table definitions, your test data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 2:04 pm
moramoga (4/21/2009)
Hi again, I did some test to check and this are my results:1. I deleted all software, hardware, material and travel records.
2. I inserted 1000$ for project TEST in the hardware table, then I run the query and the results were fine.
3.Then I inserted another 1000 for project TEST, run the query and results were good.
4.Then I inserted another 10000 for project TEST, run the query and results were good.
5.Then I inserted 1000 but for SOFTWARE, run the query and the results were wrong.
The expected results were Software total 1000 and Hardware 12000
The real results were : Software 4000 and hardware 24000
How many rows did you insert for software? How is that being joined to the rest of the query? This tells me that the relationship between these tables is not correct and you end up with twice the number of rows you should have. I would have expected software to be 2000 - not 4000, but it wouldn't be surprising that the totals actually double up.
If you read the article I link to in my signature and post the requested information we could get a working example to you very quickly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2009 at 9:45 am
Hi everyone,
This is the script of creating the objects in the database, the database is called "Example":
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TProject]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TProject](
[idProyect] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[budget] [decimal](18, 2) NOT NULL,
[year] [varchar](50) NOT NULL,
CONSTRAINT [PK_TProject] PRIMARY KEY CLUSTERED
(
[idProyect] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[THardware]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[THardware](
[idHardware] [int] IDENTITY(1,1) NOT NULL,
[idProject] [int] NOT NULL,
[month] [varchar](50) NOT NULL,
[cost] [decimal](18, 2) NOT NULL,
[description] [varchar](50) NULL,
[idResource] [varchar](50) NOT NULL,
CONSTRAINT [PK_THardware] PRIMARY KEY CLUSTERED
(
[idHardware] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TSoftware]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TSoftware](
[idSoftware] [int] IDENTITY(1,1) NOT NULL,
[idProject] [int] NOT NULL,
[month] [varchar](50) NOT NULL,
[cost] [decimal](18, 2) NOT NULL,
[commnets] [varchar](50) NOT NULL,
[item] [varchar](50) NOT NULL,
CONSTRAINT [PK_TSoftware] PRIMARY KEY CLUSTERED
(
[idSoftware] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TTravel]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TTravel](
[idTravel] [int] IDENTITY(1,1) NOT NULL,
[idProject] [int] NOT NULL,
[month] [varchar](50) NOT NULL,
[cost] [decimal](18, 2) NOT NULL,
[description] [varchar](50) NULL,
[resource] [varchar](50) NOT NULL,
CONSTRAINT [PK_TTravel] PRIMARY KEY CLUSTERED
(
[idTravel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TMaterial]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TMaterial](
[idMaterial] [int] IDENTITY(1,1) NOT NULL,
[idProject] [int] NOT NULL,
[month] [varchar](50) NOT NULL,
[cost] [decimal](18, 2) NOT NULL,
[description] [varchar](50) NULL,
CONSTRAINT [PK_TMaterial] PRIMARY KEY CLUSTERED
(
[idMaterial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_THardware_TProject]') AND parent_object_id = OBJECT_ID(N'[dbo].[THardware]'))
ALTER TABLE [dbo].[THardware] WITH CHECK ADD CONSTRAINT [FK_THardware_TProject] FOREIGN KEY([idProject])
REFERENCES [dbo].[TProject] ([idProyect])
GO
ALTER TABLE [dbo].[THardware] CHECK CONSTRAINT [FK_THardware_TProject]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TSoftware_TProject]') AND parent_object_id = OBJECT_ID(N'[dbo].[TSoftware]'))
ALTER TABLE [dbo].[TSoftware] WITH CHECK ADD CONSTRAINT [FK_TSoftware_TProject] FOREIGN KEY([idProject])
REFERENCES [dbo].[TProject] ([idProyect])
GO
ALTER TABLE [dbo].[TSoftware] CHECK CONSTRAINT [FK_TSoftware_TProject]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TTravel_TProject]') AND parent_object_id = OBJECT_ID(N'[dbo].[TTravel]'))
ALTER TABLE [dbo].[TTravel] WITH CHECK ADD CONSTRAINT [FK_TTravel_TProject] FOREIGN KEY([idProject])
REFERENCES [dbo].[TProject] ([idProyect])
GO
ALTER TABLE [dbo].[TTravel] CHECK CONSTRAINT [FK_TTravel_TProject]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TMaterial_TProject]') AND parent_object_id = OBJECT_ID(N'[dbo].[TMaterial]'))
ALTER TABLE [dbo].[TMaterial] WITH CHECK ADD CONSTRAINT [FK_TMaterial_TProject] FOREIGN KEY([idProject])
REFERENCES [dbo].[TProject] ([idProyect])
GO
ALTER TABLE [dbo].[TMaterial] CHECK CONSTRAINT [FK_TMaterial_TProject]
This is the query I am using:
SELECT p.name, SUM(s.cost) AS Software, SUM(m.cost) AS Material, SUM(t.cost) AS Travel, SUM(h.cost) AS Hardware
FROM THardware AS h
LEFT OUTER JOIN TProject AS p ON h.idProject = p.idProyect
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idProject
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
Once you start adding costs to the tables some of the values will duplicate I really dont know why, I really need your help.
April 27, 2009 at 10:02 am
Please could you supply some sample data.
I've added the following data and I don't get any duplicates.
One thing to please check is that there are no duplicate names in your TProject table...
INSERT INTO [TProject]
SELECT 'Chris test', 999.99,2008 UNION ALL
SELECT 'SomeElse ', 555.99,2007
INSERT INTO [THardware]
SELECT 2,'April',55,'tester','1' UNION ALL
SELECT 2,'May',55,'tester','1'
INSERT INTO [TSoftware]
SELECT 1,'April',66,'tester','1' UNION ALL
SELECT 1,'May',66,'tester','1'
INSERT INTO [TTravel]
SELECT 2,'April',881,'tester','1' UNION ALL
SELECT 2,'May',884,'tester','1'
INSERT INTO [TMaterial]
SELECT 1,'April',5881,'tester' UNION ALL
SELECT 1,'May',2884,'tester'
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 27, 2009 at 10:05 am
Is the project not the top level here.
Also should you query not look like this.
SELECT p.name, SUM(s.cost) AS Software, SUM(m.cost) AS Material, SUM(t.cost) AS Travel, SUM(h.cost) AS Hardware
FROM TProject AS p
LEFT OUTER JOIN THardware AS h ON p.idProyect = h.idProject
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idProject
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON p.idProyect = m.idProject
GROUP BY p.name
Sorry Lowell you've already mentioned this
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 27, 2009 at 10:23 am
Hi thanks for the quick response!
Can you please try cleaning up again the DB and trying with these values?
Insert into TProject values ('test',1000,'2008')
Insert into TSoftware values(1,'april',2000,'test','MS')
Insert into THardware values (1,'april',10000,'test','test')
Insert into TMaterial values (1,'april',2000,'test')
Insert into TSoftware values(1,'april',100000,'test','MS')
This were my results which are wrong:
test102000.004000.00NULL20000.00
They Should look like this:
test 102000.00 2000 NULL 10000.00
April 27, 2009 at 12:09 pm
Run Below:
SELECT p.name, SUM(s.cost) AS Software, SUM(cast(isnull(m.cost,0)as int)) AS Material,
SUM(t.cost) AS Travel, SUM(h.cost) AS Hardware
FROM THardware AS h
LEFT OUTER JOIN TProject AS p ON h.idProject = p.idProyect
LEFT OUTER JOIN TSoftware AS s ON p.idProyect = s.idSoftware
LEFT OUTER JOIN TTravel AS t ON p.idProyect = t.idProject
LEFT OUTER JOIN TMaterial AS m ON m.idProject = p.idProyect
GROUP BY p.name
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply