aggregate of fields in sql query result

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

SQL Server Development (2000)

aggregate of fields in sql query result

Author

Topic

manu
Starting Member

41 Posts

Posted - 2005-10-19 : 08:24:38

Dear Sir,

i have a sql query. the output of query is result of 10 records and 5 fields,group by agent id(one of the field in table having 10 distinct agent id's)

i want to have one more record for 5 aggregate values of 5 fields(i.e. 10 records and one record for aggregate of individual field having sum of 10 records for that field)

Pls help me

Munish Gupta

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-19 : 08:36:41

can you post the samples.. data for the same.. so that we can work out with it

Complicated things can be done by simple thinking

manu
Starting Member

41 Posts

Posted - 2005-10-19 : 08:50:28

sir,
let my table is
a b c d
1001 4 5 6
1001 5 5 6
1002 5 8 8

when i calculate sum group by a
the result is

a b c d
1001 9 10 12
1002 5 8 8

i want one row for aggregate sum of b,c,d for all a's ie.e

a b c d
1001 9 10 12
1002 5 8 8
14 18 20

Pls help me..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 08:52:47

Where do you want to show these data?
Are you using Reports?

Madhivanan

Failing to plan is Planning to fail

manu
Starting Member

41 Posts

Posted - 2005-10-19 : 08:55:27

yes sir exactly,

i have time value fields. reporting is for callcenter.
i need total login time for each user and total login for all users.
(user is a field)

Munish Gupta

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 08:59:14

Cant you use Summary Function in Reports?
Which Reporting Tool are you using?

Madhivanan

Failing to plan is Planning to fail

manu
Starting Member

41 Posts

Posted - 2005-10-19 : 09:10:01

sir,

i am using sql server 2000 as backend and asp for application programming.

my problem is that calculations are on time field. my values are in seconds

i have used convert(varchar(20), dateadd(s,sum(fields),108)) function for total time for each user.

now i want to find totaltime for all users.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 09:16:12

Try this


Your Select Query
Union All
Select '',sum(b),sum(c),sum(d) from
(
Your Select Query
) T

Madhivanan

Failing to plan is Planning to fail

manu
Starting Member

41 Posts

Posted - 2005-10-19 : 10:04:21

Thanks a lot sir,

it is really working properly

it is really amazing for me.

sir tell me one more thing

is it possible to display the time for more than 24 hours.

if i have time in seconds>86400 (i.e. more than 24 hours)

and using

convert(varchar(10),dateadd(s,86401,'19000101'),108)

is it possible to display it as 24:00:01

once again a lot of thanks

Munish Gupta

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 10:09:11

See my reply to your other question here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56528

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources