MVSFORUMS.com :: View topic - Get Sub total at each user level in SQL

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Get Sub total at each user level in SQL

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

Kris
Beginner

Joined: 27 May 2006
Posts: 14
Topics: 7

Posted: Wed Apr 08, 2009 3:45 am Post subject: Get Sub total at each user level in SQL

i have a data as below

Code:

Table 1
User1 Manager1
User2 Manager1
User3 Manager2
User4 Manager3

Table2
Manager1 Manager Name1
Manager2 Manager Name2
Manager3 Manager Name3

Result Table:
User1 Manager1 Manager Name1
User2 Manager1 Manager Name1
Count Of Manager Name1 - 2

User3 Manager2 Manager Name2
Count Of Manager Name2 - 1

User4 Manager3 Manager Name3
Count Of Manager Name3 - 1

Can we achieve this subtotal at user level through Query.
If we can, can you please tell me how can we do it.
Thanks!!

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose

Posted: Wed Apr 08, 2009 12:10 pm Post subject:

Kris,

Try this sql

Code:

SELECT A.USERCODE
,A.MGRCODE
,B.MGRNAME
,CHAR(' ')
FROM Table1 A
,Table2 B
WHERE A.MGRCODE = B.MGRCODE
UNION ALL
SELECT CHAR('COUNT')
,CHAR(' OF ')
,B.MGRNAME
,DIGITS(COUNT(*))
FROM Table1 A
,Table2 B
WHERE A.MGRCODE = B.MGRCODE
GROUP BY CHAR('COUNT')
,CHAR(' OF ')
,B.MGRNAME
ORDER BY 3,4

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum