Query Aggregate question

  • Hi experts,

    I am working on a data view to show HR people how many job applications we received from company's website and the job information for every job they posted out.

    For the same job_code in same job_area, the number of applications should be the same.  For example, there are 3 District Manager positions opening in Chicago North (the Job_Area), the one application submitted by applicant for this position should be count 1 in three openings.

    I have 3 tables, Mgmt_Detail_Table (application details), job_posting_table, and job_level_table

    Example data:

    Mgmt_Detail_Table   

    Applicant_ID  Received_Date   Applied_Location   JobPosting_ID

    2                 10/25/2005        Chicago North      9

    3                 10/26/2005        Southern Illiana    4

    7                 10/26/2005        Chicago North      9

    5                 10/26/2005        Southern Illiana    19

    Job_Posting_Table   

    JobPosting_ID Job_Code Job_Title                   Job_Area

    8                  4            General Manager         Chicago North

    9                  5            District Manager          Chicago North

    6                  6            Regional Director         Chicago South

    4                  5            District Manager          Southern Illiana

    7                  5            District Manager          Southern Illiana

    19                5             District Manager         Southern Illiana

    Job_Level_Table

    Job_Code    Job_Level                Job_Title

    3               Restaurant Level      Assistant Manager

    4               Restaurant Level      General Manager

    5               District Level           District Manager

    6               Market Level            Regional Director

    The result dataset should be:

    App_Count Job_Code Job_Title   JobPosting_ID   Job_Location Job_Level      District_Name Job_Area

    2              5            District Manager    4        District 1009   District Level District 1009 Southern Illiana

    0              6            Regional Director    6       Chicago South Market Level                    Chicago South

    2              5            District Manager     7       District 1008   District Level District 1008 Southern Illiana

    0              4            General Manager    8        BK02672        Restaurant Level               Chicago North

    2              5            District Manager    9        District 1018   District Level District 1018 Chicago North

    2              5            District Manager    19       District 1012  District Level District 1012 Southern Illiana

    *The job area for job posting id 4 & 19 is in 'Southern Illiana' and both postings Job_Title are for District Manager, so for both job, the App_Count should be 2.

    This is my code but is gives an error -Incorrect syntax near the keyword 'ON'.

    SELECT     ISNULL(M.App_Count, 0) AS App_Count, P.Job_Code, P.Job_Title, P.JobPosting_ID, P.Job_Location, P.Job_Posting_Date, P.Job_Start_Date,

                          L.Job_Level, P.District_Name, P.Job_Area, P.Job_Posting_Status

    FROM         (SELECT     B.Job_Code, A.Applied_Location, COUNT(*) AS App_Count

                           FROM          Mgmt_App_Detail_Table A INNER JOIN

                                             ob_Posting_Table B ON A.JobPosting_ID = B.JobPosting_ID

                           GROUP BY B.Job_Code, A.Applied_Location) M RIGHT OUTER JOIN

                          Job_Level_Table L INNER JOIN

                          Job_Posting_Table P ON L.Job_Code = P.Job_Code ON L.Job_Code = M.Job_Code ON P.Job_Area=M.Job_Area

    If I take out the last 'ON P.Job_Area=M.Job_Area', then query returns many duplicated records.....WHAT SHOULD I DO TO MY CODE SO THAT I CAN DISPLAY CORRECT RECORDS?  THANK YOU IN ADVANCE.

  • In the future, please post "CREATE TABLE" and Insert statments, not the output of a select.

    There are a number of problems with your SQL:

    1. Based the example tables and the SQL, the SQL shows other errors such as references to columns that do not exist.

    2. You are expecting counts by Job_Code,Job_Title and Job_Location but have included JobPosting_ID in the result set.

    3. The example data does not match the expected result.

    Try this as a starting point:

    SELECT COALESCE (M.App_Count, 0) AS App_Count

    , P.Job_Code

    , P.Job_Title

    , Job_Level_Table.Job_Level

    , P.Job_Area

    FROM Job_Level_Table

    JOIN(SELECT DISTINCT

    Job_Posting_Table.Job_Code

    , Job_Posting_Table.Job_Title

    , Job_Posting_Table.Job_Area

    from Job_Posting_Table

    ) as P

    on P.Job_Code = Job_Level_Table.Job_Code

    LEFT OUTER JOIN

    (SELECT Job_Posting_Table.Job_Code

    , Job_Posting_Table.Job_Title

    , Job_Posting_Table.Job_Area

    , COUNT(*) App_Count

    FROM Job_Posting_Table

    JOINMgmt_Detail_Table

    On Mgmt_Detail_Table.JobPosting_ID = Job_Posting_Table.JobPosting_ID

    GROUP BY Job_Posting_Table.Job_Code

    , Job_Posting_Table.Job_Title

    , Job_Posting_Table.Job_Area

    ) as M

    ON M.Job_Code= P.Job_Code

    AND M.Job_Title= P.Job_Title

    ANDM.Job_Area= P.Job_Area

    go

    create table Job_Level_Table

    ( Job_Code integer not null

    , Job_Level varchar(255) not null

    , Job_Title varchar(255) not null

    , constraint Job_Level_Table_P primary key (Job_Code)

    , constraint Job_Level_Table_U_Job_Title unique (Job_Title)

    )

    go

    create table Job_Posting_Table

    ( JobPosting_ID integer not null

    , Job_Code integer not null

    , Job_Title varchar(255) not null

    , Job_Area varchar(255) not null

    , constraint Job_Posting_Table_P primary key (JobPosting_ID)

    , constraint Job_Level_Table_FK_Job_Posting_Table foreign key (Job_Code ) references Job_Level_Table

    )

    go

    create table Mgmt_Detail_Table

    ( Applicant_ID integer not null

    , Received_Date datetime not null

    , Applied_Location varchar(255) not null

    , JobPosting_IDintegernot null

    , constraint Mgmt_Detail_Table_P primary key (Applicant_ID)

    , constraint Job_Posting_Table_FK_Mgmt_Detail_Table foreign key (JobPosting_ID ) references Job_Posting_Table

    )

    go

    insert into Job_Level_Table

    (Job_Code ,Job_Level , Job_Title)

    select 3 , 'Restaurant Level' ,'Assistant Manager' union all

    select 4 , 'Restaurant Level' , 'General Manager' union all

    select 5 , 'District Level' , 'District Manager' union all

    select 6 , 'Market Level' , 'Regional Director'

    go

    insert into Job_Posting_Table

    (JobPosting_ID , Job_Code , Job_Title , Job_Area)

    select 8 , 4 , 'General Manager' ,'Chicago North' union all

    select 9 , 5 , 'District Manager' ,'Chicago North' union all

    select 6 , 6 , 'Regional Director','Chicago South' union all

    select 4 , 5 , 'District Manager','Southern Illiana' union all

    select 7 , 5 , 'District Manager','Southern Illiana' union all

    select 19, 5 , 'District Manager','Southern Illiana'

    go

    insert into Mgmt_Detail_Table

    (Applicant_ID , Received_Date, Applied_Location , JobPosting_ID)

    select 2 , '10/25/2005' , 'Chicago North' , 9union all

    select 3 , '10/26/2005' , 'Southern Illiana' , 4 union all

    select 7 , '10/26/2005' , 'Chicago North' , 9 union all

    select 5 , '10/26/2005' , 'Southern Illiana' , 19

    go

    SQL = Scarcely Qualifies as a Language

  • Carl, thank you so much.  It works perfectly.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply