Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Problem with sub querying

    I have 3 queries i'm trying to compile into one query

    The 3 queries are as per below:

    Open Count By Engineer:

    Select
    concat(firstname, " ", lastname) AS Engineer, count(*)
    from
    ost_ticket
    left join ost_staff on ost_ticket.staff_id = ost_staff.staff_id
    LEFT JOIN ost_department ON ost_ticket.dept_id = ost_department.dept_id
    WHERE
    (ost_ticket.status = "open")
    AND
    (ost_ticket.ispending = 0)
    AND
    (ost_staff.dept_id = 1 OR ost_staff.dept_id = 12)
    GROUP BY Engineer
    ORDER BY ost_staff.dept_id desc, engineer asc


    Pending Count By Engineer:

    Select
    concat(firstname, " ", lastname) AS Engineer, count(*)
    from
    ost_ticket
    left join ost_staff on ost_ticket.staff_id = ost_staff.staff_id
    LEFT JOIN ost_department ON ost_ticket.dept_id = ost_department.dept_id
    WHERE
    (ost_ticket.ispending = 1)
    AND
    (ost_staff.dept_id = 1 OR ost_staff.dept_id = 12)
    GROUP BY Engineer
    ORDER BY ost_staff.dept_id desc, engineer asc


    Closed Count By Engineer:

    Select
    concat(firstname, " ", lastname) AS Engineer,
    count(*)
    from
    ost_ticket
    left join ost_staff on ost_ticket.staff_id = ost_staff.staff_id
    WHERE
    (ost_ticket.status = "closed")
    AND
    (ost_ticket.ispending = 0)
    AND
    (ost_ticket.Closed LIKE '2011-06%')
    AND
    (ost_staff.dept_id = 1 OR ost_staff.dept_id = 12)
    GROUP BY Engineer
    ORDER BY ost_staff.dept_id desc, engineer asc


    Can anyone help with this. i have tried numerous different ways but all have failed and am new to SQL although i have to start picking it up as a main job shortly

    Many Thanks

    Marcus

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you lost me right here --

    from
    ost_ticket
    left join ost_staff on ost_ticket.staff_id = ost_staff.staff_id


    since it's a left outer join, this returns every ticket, regardless of whether there's a staff related to a ticket

    in other words, your tickets have missing or invalid staffs

    similarly, your tickets have missing or invalid departments

    if this is not the case, make them INNER JOINs



    the good news is, you can use SUM() and COUNT() on CASE expressions, and your three queries are easy to combine since they have the same FROM and GROUP BY clauses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    4

    Thanks

    Thanks for the advise.

    How would i re-write the query to get the result set im looking for then as im still at a loss.

    The more im reading SQL the more im understanding it but this one is bugging me.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i'll walk you through it

    first, please write just one query that has the FROM and GROUP BY and ORDER BY clauses that you need, plus SELECT * for all columns (we'll fix this part later)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    OK.

    so i have

    Select *
    from
    ost_ticket
    left join ost_staff on ost_ticket.staff_id = ost_staff.staff_id
    WHERE
    (ost_ticket.status = "closed")
    AND
    (ost_ticket.ispending = 0)
    AND
    (ost_ticket.Closed LIKE '2011-06%')
    AND
    (ost_staff.dept_id = 1 OR ost_staff.dept_id = 12)
    GROUP BY Engineer
    ORDER BY ost_staff.dept_id desc, engineer asc

    (had to leave as left join as although the tickets all have staff id sometimes the staff id is invalid. This only occurs with 0 as its to symbolise unassigned)

  6. #6
    Join Date
    Jun 2011
    Posts
    4
    Select *
    from
    ost_ticket
    left join ost_staff on ost_ticket.staff_id = ost_staff.staff_id
    WHERE
    (ost_ticket.status = "closed")
    AND
    (ost_ticket.ispending = 0)
    AND
    (ost_ticket.Closed LIKE '2011-06%')
    AND
    (ost_staff.dept_id = 1 OR ost_staff.dept_id = 12)
    GROUP BY ost_ticket.staff_id
    ORDER BY ost_staff.dept_id desc, ost_ticket.staff_id asc

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, this is what i had in mind --
    Code:
    SELECT *
      FROM ost_ticket
    LEFT OUTER
      JOIN ost_staff 
        ON ost_staff.staff_id = ost_ticket.staff_id
       AND ost_staff.dept_id IN ( 1 , 12 )
    GROUP 
        BY ost_ticket.staff_id
    ORDER 
        BY ost_staff.dept_id DESC
         , ost_ticket.staff_id ASC
    when you run this query, please confirm that all the rows to produce the different counts that you want to get are included
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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