|
View previous topic :: View next topic
|
| Author |
Message |
sunnybunny
New User
Joined: 29 Nov 2005
Posts: 49
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sunnybunny
New User
Joined: 29 Nov 2005
Posts: 49
|
|
|
|
Hello,
But I need SQL query for my requirement.
Please provide the same.
Regards,
Sunny. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| 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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| I was talking physically, as in does anyone in your company serve two masters? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| I thought maybe one or two, thanks for the enlightenment |
|
| Back to top |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| Dick, no it doesn't. I'll fix it and post the correct query |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Thank you, kind sir 
d |
|
| Back to top |
|
|
|
|
