Portal | IBM Manuals | Downloads | Products | Refer | Info | Programs | JCLs | Forum Rules*| Site Map | Mainframe CD 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index
 
Register
 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index FAQ Search Memberlist Usergroups Profile Log in to check your private messages Log in
 
Query needed

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
Author Message
sivatechdrive

Active User


Joined: 17 Oct 2004
Posts: 168
Location: hyderabad
PostPosted: Tue Nov 27, 2007 7:03 pm    Post subject: Query needed
 Reply with quote
Hi all

There are 2 fields in my table and I have the data in my table as below
PROJCODE EMPNAME
====================
NAL001 SIVA
NAL001 PRASAD
NAL001 RAMESH
NAL001 SIVA
NAL001 RAMESH
GAL001 ABC
GAL001 ABC
GAL001 ABC
GAL001 ABC
GAL001 ABC
PAL001 CAD
PAL001 CAD
PAL001 NSC
PAL001 NSC
PAL001 NSC


THERE ARE 5 EMPLOYEES FOR EACH PROJECT & i WANT THE COUNT OF EMPLOYEES WITH OUT DUPLICATES
PROJCODE COUNT
NAL001 3 (AS SIVA,RAMESH ARE REPEATING)
GAL001 1 (AS ABC IS THE ONLY THING PRESENT)
PAL001 2 (AS CAD,NSC ARE REPEATING)


PLEASE HELP ME

THANKS N REGARDS
PRASAD
Back to top
View user's profile Send private message
References
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1001
Location: Virginia, USA
PostPosted: Tue Nov 27, 2007 7:22 pm    Post subject:
Reply with quote
Code:
SELECT PROJCODE, COUNT(*) FROM
(SELECT DISTINCT PROJCODE, EMPNAME FROM TABLE) A
GROUP BY PROJCODE;
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 185
Location: Chennai
PostPosted: Tue Nov 27, 2007 7:22 pm    Post subject: Reply to: Query needed
 Reply with quote
This may help you

Code:
Select  PROJCODE,COUNT(DISTINCT EMPNAME )
From Table A


Please off CAPS before you post
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1