Enlightensoft's Blog

Helping in your each step

  • Categories

  • Authors

Aggregate functions with extra column in select

Posted by Pankil Patel on October 31, 2012

How to select extra column(s) with aggregate function in query?

  1. Rollback script:
    ALTER TABLE EMP_DEPARTMENT DROP CONSTRAINT UNIQUE_1;
    DROP TABLE EMP_DEPARTMENT;
    DROP TABLE EMPLOY;
    DROP TABLE DEPARTMENT;
  2. Create script:
    CREATE TABLE “TSSRISKAPPDBO”.”EMPLOY”
    (
    “EMPLOY_ID”   NUMBER(10,0),
    “EMPLOY_NAME” VARCHAR2(100 BYTE),
    “SALARY”      NUMBER(10,2),
    CONSTRAINT “PK_EMPLOY” PRIMARY KEY (“EMPLOY_ID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE “AMRISKWLADBO_DATA” ENABLE
    )
    SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “AMRISKWLADBO_DATA” ;

    CREATE TABLE “TSSRISKAPPDBO”.”DEPARTMENT”
    (
    “DEPARTMENT_ID”   NUMBER(10,0),
    “DEPARTMENT_NAME” VARCHAR2(100 BYTE),
    CONSTRAINT “PK_DEPARTMENT” PRIMARY KEY (“DEPARTMENT_ID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE “AMRISKWLADBO_DATA” ENABLE
    )
    SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “AMRISKWLADBO_DATA” ;

    CREATE TABLE “TSSRISKAPPDBO”.”EMP_DEPARTMENT”
    (
    “EMPLOY_ID”     NUMBER(10,0),
    “DEPARTMENT_ID” NUMBER(10,0),
    CONSTRAINT “UNIQUE_1” UNIQUE (“EMPLOY_ID”, “DEPARTMENT_ID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE “AMRISKWLADBO_DATA” ENABLE,
    CONSTRAINT “FK_EMP” FOREIGN KEY (“EMPLOY_ID”) REFERENCES “TSSRISKAPPDBO”.”EMPLOY” (“EMPLOY_ID”) ENABLE,
    CONSTRAINT “FK_DEPT” FOREIGN KEY (“DEPARTMENT_ID”) REFERENCES “TSSRISKAPPDBO”.”DEPARTMENT” (“DEPARTMENT_ID”) ENABLE
    )
    SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “AMRISKWLADBO_DATA” ;

  3. Data creation:
    INSERT INTO EMPLOY VALUES (1,’VIBHA’,21);
    INSERT INTO EMPLOY VALUES (2,’PANKIL’,10.2);
    INSERT INTO EMPLOY VALUES (3,’MAYANK’,15);
    INSERT INTO EMPLOY VALUES (4,’NAMRATA’,12);
    INSERT INTO EMPLOY VALUES (5,’NISHA’,9);
    INSERT INTO EMPLOY VALUES (6,’SHAILESH’,12);

    INSERT INTO DEPARTMENT VALUES (1,’APPS’);
    INSERT INTO DEPARTMENT VALUES (2,’UET’);
    INSERT INTO DEPARTMENT VALUES (3,’PROCESS’);
    INSERT INTO DEPARTMENT VALUES (4,’BPO’);

    INSERT INTO EMP_DEPARTMENT VALUES (1,1);
    INSERT INTO EMP_DEPARTMENT VALUES (2,2);
    INSERT INTO EMP_DEPARTMENT VALUES (3,3);
    INSERT INTO EMP_DEPARTMENT VALUES (4,4);
    INSERT INTO EMP_DEPARTMENT VALUES (5,4);
    INSERT INTO EMP_DEPARTMENT VALUES (6,1);
    INSERT INTO EMP_DEPARTMENT VALUES (6,3);
    INSERT INTO EMP_DEPARTMENT VALUES (4,1);
    INSERT INTO EMP_DEPARTMENT VALUES (4,2);
    INSERT INTO EMP_DEPARTMENT VALUES (6,2);

  4. Query: Get Employee(s) data of employee(s) who has the highest Salary in “BPO” department

    SELECT *
    FROM EMPLOY E,
    (SELECT MAX(SALARY) AS MAX_SALARY
    FROM EMPLOY
    WHERE EMPLOY_ID IN
    (SELECT EMPLOY_ID
    FROM EMP_DEPARTMENT
    WHERE DEPARTMENT_ID =
    (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE DEPARTMENT_NAME = ‘BPO’
    )
    )
    ) MAX_RESULT
    WHERE MAX_RESULT.MAX_SALARY = E.SALARY
    AND E.EMPLOY_ID            IN
    (SELECT EMPLOY_ID
    FROM EMP_DEPARTMENT
    WHERE DEPARTMENT_ID =
    (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE DEPARTMENT_NAME = ‘BPO’
    )
    );

 

Note: Summary of above code is: get inner join of any aggregate function’s result with the table field

Apply where condition at both places (In inner query having aggregate function  and In outer query with table)

Advertisements

2 Responses to “Aggregate functions with extra column in select”

  1. Leslie said

    Do you mind if I quote a few of your posts as long as I
    provide credit and sources back to your website? My blog is in the very same area of
    interest as yours and my users would definitely benefit
    from some of the information you present here. Please let me know if this ok with you.
    Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: