IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL MAX function to find Max employees


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Moderator Emeritus


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

PostPosted: Wed Oct 10, 2007 11:04 pm
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
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

Moderator Emeritus


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

PostPosted: Thu Oct 11, 2007 12:16 am
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: 631
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 7:52 am
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

Moderator Emeritus


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

PostPosted: Thu Oct 11, 2007 11:37 am
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: 631
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 5:08 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Thu Oct 11, 2007 5:14 pm
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

Moderator Emeritus


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

PostPosted: Thu Oct 11, 2007 6:21 pm
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: 631
Location: Wisconsin

PostPosted: Thu Oct 11, 2007 6:21 pm
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

Moderator Emeritus


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

PostPosted: Thu Oct 11, 2007 6:33 pm
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: 631
Location: Wisconsin

PostPosted: Fri Oct 12, 2007 4:45 pm
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
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

Moderator Emeritus


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

PostPosted: Tue Oct 16, 2007 9:42 pm
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
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: 631
Location: Wisconsin

PostPosted: Tue Oct 16, 2007 10:08 pm
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

Moderator Emeritus


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

PostPosted: Wed Oct 17, 2007 12:23 am
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
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

Moderator Emeritus


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

PostPosted: Wed Oct 17, 2007 12:44 am
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: 631
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 9:16 am
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
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 9:29 am
Reply with quote

Correct query is this:

Code:

    SELECT NAME, SUM(1) As CreatorCount
    FROM   SYSIBM.SYSTABLES                                                     
    WHERE CREATOR IN ('OSDB2','OSMARI2')                                       
    GROUP BY NAME   
    HAVING SUM(1) > 1
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Oct 17, 2007 7:59 pm
Reply with quote

Thank you, kind sir icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts sort to find out the char which repea... Mainframe Interview Questions 10
No new posts Compare 2 alphabets in a REXX and fin... CLIST & REXX 2
No new posts COBOL 6.4 - User Defined Function nee... COBOL Programming 6
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Calling an Open C library function in... CICS 1
Search our Forums:

Back to Top