sum summary 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 2000 Forums

Transact-SQL (2000)

sum summary query

Author

Topic

mkh
Starting Member

18 Posts

Posted - 2003-06-16 : 08:03:12

I have a table as follows

nameid,date, task, subtask, time
i want to find the sum of 'time 'spent for x no of days task wise and subtask wise, such that my result shoulbd be as:

nameid task subtask subbtasktime tasktime
111 A AA 5
111 A AB 5 10

can anyone give query for such output?

thanks

mkh

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-16 : 09:18:52

I might not be understanding your data but ....


select
    t1.nameid,
    t1.task,
    t2.task,
    sum(t1.time) + sum(t2.time)
from
    table t1
    inner join table table t2
        on t1.subtask = t2.task and
           t1.task <> t2.subtask
group by
    t1.nameid,
    t1.task,
    t2.task

I think you query is going to end up looking something like this. What does the row look like for task AA? Is there a subtask? Is it null?

Jay White
{0}

mkh
Starting Member

18 Posts

Posted - 2003-06-16 : 09:43:21

No you havent understood my query , there is only 1 table:
these are col names.
nameid, task, subtask, subbtasktime, tasktime
this is data, iwant in the output.
111 , A, AA , 5, -
111 , A, AB , 5, 10

I want to calculate task time and subtask time for a certain period.
so total time spent on each sub tasks is 5
and total time spent on task A is (5 +5)=10

i hope this makes it clear.
thks
mkh

mkh
Starting Member

18 Posts

Posted - 2003-06-17 : 02:43:21

I think i need a self-join
any ideas?

mkh

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-17 : 03:10:53

I think that your data model - having everything in one table - makes this query far more complex than it needs to be. If your sub tasks were split away from the main tasks I would find this far easier.

Either way Jay's query still looks a sound basis for what you are trying to achieve.

Though I guess that this line

quote:
inner join table table t2

Should actually read

quote:
inner join table t2

As it is the self join you are looking for.

-------
Moo.

mkh
Starting Member

18 Posts

Posted - 2003-06-17 : 04:42:52

Actually i get am empty set using the above query
also t1.subtask = t2.task will never be same!

mkh
Starting Member

18 Posts

Posted - 2003-06-17 : 04:49:39

as the fields task & subtask are relevant to each other I cant split them also
the main field time is part of sub task which is part of a task.
so i just want to sum per task & per sub task.
sounds easy ?
Like the english query for this would be :
Give me total time taken by emp A per task per sub teak.
any direct ans?

ytsejam
Starting Member

1 Post

Posted - 2003-06-17 : 07:06:22

111 A AA 5
111 A AB 5 10

WHAT IS 10? IS IT SUM OF 5`S WHICH ARE IN THE TABLE?
IS TASK TIME AGREGATE OF SUBTASKTIMES OR WHAT?
WHY IS FIRST ROW OF THE TABLE HAS NULL VALUE IN THE TASKTIME FIELD?
IF YOU ANSWER THESE QUESTIONS, I`LL TRY TO HELP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources