Summing with a sub-query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Summing with a sub-query?

Author

Topic

richie_b_97
Starting Member

8 Posts

Posted - 2008-04-30 : 11:06:26

Hi,

I'm trying to get a sum but not doing too well. I think I need a subquery but am unsure how to phrase it.

Problem:
I need to sum timesheet hours logged at work-code level to project-level (for named projects), where a project consists of 0-to-many work-codes. The 'Project' table is used for both projects and work-codes; the 'pr_code' contains the unique code (i.e. the work-code or the project-code), 'pr_master' field contains the parent. The Timesheet table will contain pr_code's for work-codes, but won't contain an entry for a work-code if no-one has logged any time to a work-code.

Sample input:

Timesheet table
===============
pr_code|ts_hours
QWER.01|6
QWER.01|7
QWER.02|3
QWET.01|2

Project table
=============
pr_code|pr_master
QWER.01|QWER
QWER.01|QWER
QWER.02|QWER
QWET.01|QWET
QWER|QQQQ
QWET|QQQQ
QWEY|QQQQ

Intended output:
For named projects QWER, QWET & QWEY:

QWER|16
QWET|2
QWEY|0

I've got the following so far which almost gets there, but appears to be summing up as it goes i.e. QWER=16, QWET=18, QWEY=18:

SELECT p1.PR_Master AS Expr1, SUM(Timesht.TS_Hours) AS Expr2
FROM Timesheet LEFT OUTER JOIN
Projects ON Timesheet.PR_Code = Projects.PR_Code LEFT OUTER JOIN
Projects p1 ON Timesht.PR_Code = p1.PR_Code
WHERE (p1.PR_Master IN ('QWER', 'QWET', 'QWEY'))
GROUP BY p1.PR_Master

Any help most appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 11:23:39

[code]SELECT t.pr_code,
SUM(CASE WHEN t1.Hours IS NULL THEN 0 ELSE t1.Hours END) AS TotalHours
FROM (SELECT pr_code FROM Project WHERE pr_master='QQQQ')t
LEFT JOIN (SELECT DISTINCT pr_code,pr_master FROM Project) p
ON p.pr_master=t.pr_code
LEFT JOIN (SELECT pr_code,SUM(ts_hours) AS Hours
FROM Timesheet
GROUP BY pr_code)t1
ON t1.pr_code=p.pr_code
GROUP BY t.pr_code[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-30 : 11:46:13

Another option...

SELECT a.pr_code, SUM(ISNULL(ts_hours, 0)) AS TotalHours FROM @Project a
    LEFT OUTER JOIN (SELECT DISTINCT * FROM @Project) b ON a.pr_code = b.pr_master
    LEFT OUTER JOIN @Timesheet c ON b.pr_code = c.pr_code
WHERE a.pr_master = 'QQQQ'
GROUP BY a.pr_code

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

richie_b_97
Starting Member

8 Posts

Posted - 2008-05-01 : 03:13:11

Thanks for the replies, however they don't give the intended output...

I need to group by pr_master, where a list of pr_master's is given - so for the tables I've defined above, the Timesheet table needs to have summed up all entries for QWER.001 and QWER.002, and output the summed total up to their parent project (see intended output).

SELECT Projects.PR_Master, SUM(ISNULL(ts_hours, 0)) AS 'TotalHours'
FROM Projects INNER JOIN
Timesht ON Projects.PR_Code = Timesht.PR_Code
WHERE (Projects.PR_Master IN ('QWER','QWET','QWEY'))
GROUP BY Projects.PR_Master

The above is almost there, but gives the ouput:
QWER|16
QWET|18
QWEY|18

i.e. is cumulative, rather than the correct answer:
QWER|16
QWET|2
QWEY|0

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-01 : 04:58:33

Try this...

-- Structure and data
declare @Timesheet table (pr_code varchar(10), ts_hours int)
insert @Timesheet
          select 'QWER.01', 6
union all select 'QWER.01', 7
union all select 'QWER.02', 3
union all select 'QWET.01', 2

declare @Project table (pr_code varchar(10), pr_master varchar(10))
insert @Project
          select 'QWER.01', 'QWER'
union all select 'QWER.01', 'QWER'
union all select 'QWER.02', 'QWER'
union all select 'QWET.01', 'QWET'
union all select 'QWER', 'QQQQ'
union all select 'QWET', 'QQQQ'
union all select 'QWEY', 'QQQQ'

-- Inputs
declare @pr_masters table (pr_master varchar(10))
insert @pr_masters select 'QWER' union select 'QWET' union select 'QWEY'

-- Calculation
SELECT a.pr_master, SUM(ISNULL(ts_hours, 0)) AS TotalHours FROM @pr_masters a
    LEFT OUTER JOIN (SELECT DISTINCT * FROM @Project) b ON a.pr_master = b.pr_master
    LEFT OUTER JOIN @Timesheet c ON b.pr_code = c.pr_code
GROUP BY a.pr_master

/* Results
pr_master  TotalHours
---------- -----------
QWER       16
QWET       2
QWEY       0
*/

quote:
however they don't give the intended output

Both previous suggestions gave the output you expected for the example you supplied. If you still don't get the 'intended output', you should supply an example for which the suggestions supplied don't work for what you need. Otherwise, we're just guessing.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

richie_b_97
Starting Member

8 Posts

Posted - 2008-05-01 : 10:55:09

Hi Ryan,
Thanks for the help and the perseverance. The query worked - I needed an extra 'where' clause on my actual data, which gave me something other than expected. Breaking it down into var tables also helped my understanding more.

Your help is much appreciated - it's saved a few people several days a year of some very, very tedious work.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources