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

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

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.

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)

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)

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

actually, this is what i had in mind --when you run this query, please confirm that all the rows to produce the different counts that you want to get are included