Results 1 to 4 of 4
  1. #1
    Join Date
    May 2017
    Posts
    2
    Provided Answers: 1

    Answered: Union Join Query

    I have 2 tables, a table called import and another called import_archive.
    The tables have the same structure, the archive table contains archived records from the import table.

    I need to query both tables to get data relating to how many jobs with a Job_Work_Type of 'PPD' were done in each week of the year.

    If I use the following code....

    (SELECT
    WEEKOFYEAR(Job_Start_Date) AS weekno,
    COUNT(1) AS Jobs,
    SUBDATE(Job_Start_Date, INTERVAL WEEKDAY(Job_Start_Date) DAY) AS Week_Start_Date
    FROM import where Job_Work_Type='PPD'
    GROUP BY WEEKOFYEAR(Job_Start_Date))
    union

    (SELECT
    WEEKOFYEAR(Job_Start_Date) AS weekno,
    COUNT(1) AS Jobs,
    SUBDATE(Job_Start_Date, INTERVAL WEEKDAY(Job_Start_Date) DAY) AS Week_Start_Date
    FROM import_archive where Job_Work_Type='PPD'
    GROUP BY WEEKOFYEAR(Job_Start_Date))

    It shows the output I need but it does not show the data from both tables added together, by this I mean I need the number of jobs for both tables added together so I only have 52 weeks of data per year and not 104.

    I hope this makes sense.

    Regards

    Alan Edwards

  2. Best Answer
    Posted by cardscale

    "Thanks, this has solved my problem, really appreciate it.

    Regards

    Alan"


  3. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    so wrap it up.

    Code:
    select col, sum(other_col) from (
    (SELECT
     WEEKOFYEAR(Job_Start_Date) AS weekno, 
     COUNT(1) AS Jobs,
     SUBDATE(Job_Start_Date, INTERVAL WEEKDAY(Job_Start_Date) DAY) AS Week_Start_Date
     FROM import where Job_Work_Type='PPD'
     GROUP BY WEEKOFYEAR(Job_Start_Date))
     union
    
     (SELECT
     WEEKOFYEAR(Job_Start_Date) AS weekno, 
     COUNT(1) AS Jobs,
     SUBDATE(Job_Start_Date, INTERVAL WEEKDAY(Job_Start_Date) DAY) AS Week_Start_Date
     FROM import_archive where Job_Work_Type='PPD'
     GROUP BY WEEKOFYEAR(Job_Start_Date))
    ) as a
    group by ...

  4. #3
    Join Date
    May 2017
    Posts
    2
    Provided Answers: 1

    Thanks

    Thanks, this has solved my problem, really appreciate it.

    Regards

    Alan

  5. #4
    Join Date
    Jan 2017
    Posts
    18
    You can check the code

    SELECT Name,Gender,Salary,DepartmentName
    FROM tblEmployee
    INNER JOIN tblDepartment
    ON tblEmployee.DepartmentId=tblDepartment.Id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •