Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL MAX function to find Max employees
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sunnybunny

New User


Joined: 29 Nov 2005
Posts: 49

PostPosted: Wed Oct 10, 2007 9:24 pm    Post subject: SQL MAX function to find Max employees
Reply with quote

Hello friends,

I have one requirement related to SQL MAX function.

let us say there are 10 employee numbers in a table ABC. Each employee is related to one or more departments in same table ABC. Now I want to find out which employee is realted to maximum number of departments.

Please provide me the SQL query for this.

Regards,
Sunny.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 10, 2007 11:04 pm    Post subject:
Reply with quote

Hello,

Your requirement is not the typical use of MAX. . .

MAX is typically used to retrieve the maximum value of a column.
Back to top
View user's profile Send private message
sunnybunny

New User


Joined: 29 Nov 2005
Posts: 49

PostPosted: Wed Oct 10, 2007 11:28 pm    Post subject:
Reply with quote

Hello,

But I need SQL query for my requirement.
Please provide the same.

Regards,
Sunny.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 11, 2007 12:16 am    Post subject:
Reply with quote

Hello,

Quote:
Each employee is related to one or more departments in same table ABC.
If this is an employee table and a row contains a dept for each employee, how might there be multiple departments for one employee?

It may help if you post the table definition (at least the colums needed) and your 10 sets of values for those columns.

Also post the result you want.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 7:52 am    Post subject:
Reply with quote

Making some assumptions about your table:
Each entry in the table represents an employee/dept pair. If an employee is in more than one dept their employee number would appear more than once. If that is the case you could do this

Code:

SELECT EmployeeNumber, MAX(SUM(1)) as 'Number of Depts'
From ABC
Group By EmployeeNumber
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 11, 2007 11:37 am    Post subject:
Reply with quote

Hi Steve,

Might this be homework we are looking at?

If there are multiple employees with the same number of depts that they are "in", will all 3 be selected or only an unpredictable one of them be selected?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 5:08 pm    Post subject:
Reply with quote

I would lean towards unpredictable. If the table is truely like this though, it is poorly designed for an operation table. It seems like the natural primary key would be emplyee number and there would be a different table that indicated department membership. Also, how many places have you been where employees belong to multiple departments?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Oct 11, 2007 5:14 pm    Post subject: Reply to: SQL MAX function to find Max employees
Reply with quote

Quote:
.... Also, how many places have you been where employees belong to multiple departments?


at least this company has in the database design one row for each employee department.

I' ve seen worst companies around here,
one of them had all the department on the same row..

go figure !
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 11, 2007 6:21 pm    Post subject:
Reply with quote

Hello,

Quote:
how many places have you been where employees belong to multiple departments
Well, i've worked several places that were manufacturing companies and the mill workers might work in multiple "jobs" during a pay perioid. Different jobs paid differently and many were department specific.

When worked hours were extended to gross for payroll, the job "master" help the hourly rate (or contract rate rules) for that job.

While these folks "worked in multiple departments", they only belonged to one.

As i mentioned before - homework?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 6:21 pm    Post subject:
Reply with quote

I was talking physically, as in does anyone in your company serve two masters?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 11, 2007 6:33 pm    Post subject:
Reply with quote

Hello,

Yes, they serve the "master" of the department where the job is located.

If you are working on these shop floors, you are under the direction of that supervisor while you are performing that job.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Oct 12, 2007 4:45 pm    Post subject:
Reply with quote

I thought maybe one or two, thanks for the enlightenment
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Oct 16, 2007 9:36 pm    Post subject: Reply to: SQL MAX function to find Max employees
Reply with quote

Looked something new and tried creating similar rows in my table, but I am getting the follow error:
Code:
DSNT408I SQLCODE = -112, ERROR:  THE OPERAND OF AN AGGREGATE FUNCTION INCLUDES
         AN AGGREGATE FUNCTION OR A SCALAR FULLSELECT                         
DSNT418I SQLSTATE   = 42607 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNXOOS1 SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = -220 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION           
DSNT416I SQLERRD    = X'FFFFFF24'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION   


Thanks,
Viji
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 16, 2007 9:42 pm    Post subject:
Reply with quote

Hello Viji,

Is your error post related to the MAX topic?

If this is part of the MAX discussion, we can leave it in this topic, it if is a new question, let us know and it can be moved to a new topic.

Please post your sql that generated the error.
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue Oct 16, 2007 10:03 pm    Post subject: Reply to: SQL MAX function to find Max employees
Reply with quote

Yes. it is related to this MAX topic. I want to try using the
MAX(SUM(1)) function ..here is my query

SELECT NAME, MAX(SUM(1)) as creators
from sysibm.systables
where Creator in ('OSDB2','MSCREI')
group by Name;

I was trying to retreive the table which has more than one creators.

This is just for my understanding.
Thanks,
Viji
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Oct 16, 2007 10:08 pm    Post subject:
Reply with quote

Eh, my query may be wrong. But a table can't have more than one creator, can it?

The primary key on systables is CREATOR, NAME. That means one creator per table.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 17, 2007 12:23 am    Post subject:
Reply with quote

Hello,

You might try changing this
Code:
SELECT NAME, MAX(SUM(1)) as creators
to
Code:
SELECT NAME, MAX(SUM(1)) as 'creators'
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Wed Oct 17, 2007 12:34 am    Post subject: Reply to: SQL MAX function to find Max employees
Reply with quote

When I give quotes for creators, it is giving me this error
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "'creators'".

without any "AS" field the error remains the same.
Code:
    SELECT NAME,MAX(SUM(1))                                                     
    FROM   SYSIBM.SYSTABLES                                                     
    WHERE CREATOR IN ('OSDB2','OSMARI2')                                       
    GROUP BY NAME;                                                             

DSNT408I SQLCODE = -112, ERROR:  THE OPERAND OF AN AGGREGATE FUNCTION INCLUDES 
         AN AGGREGATE FUNCTION OR A SCALAR FULLSELECT                           


I beleive MAX and SUM function cannot be used as one.
Thanks,
Viji
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 17, 2007 12:44 am    Post subject:
Reply with quote

Hello,

Steve - the example from earlier in the post runs successfully?
Quote:
Code:
SELECT EmployeeNumber, MAX(SUM(1)) as 'Number of Depts'
From ABC
Group By EmployeeNumber
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 9:16 am    Post subject:
Reply with quote

Dick, no it doesn't. I'll fix it and post the correct query
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Find out a active PGM jpsager JCL & VSAM 10 Fri Oct 07, 2016 4:32 pm
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us