I am trying to get mySQL to allow me to join multiple queries. Here is an example of the query that I am running:

select a.*, cnt1 from
(SELECT item_department_id totID,count(*) totCnt FROM helpdesk_items where item_created>='2006-01-01' group by item_department_id) a
left outer join (select item_department_id id1, item_calltype, count(*) cnt1 from helpdesk_items where item_created>='2006-01-01' and item_calltype=0 group by item_department_id,item_calltype) b
on totID=id1

A sample table of helpdesk_items is:

item_department_id | item_calltype
----------------------------------------------------
1 | 5
2 | 6
2 | 7
1 | 5
1 | 5
1 | 5
2 | 0
2 | 0
1 | 5

I want my results to look something like:

totID | totCnt | cnt1
-------------------------------------------------
1 | 5 | Null
2 | 4 | 2

I have done queries using this same logic on SQLServer and it works everytime. Does anyone have any idea as to what is wrong with this or how to fix it so that I can achieve the results that are listed.

All versions of MySQL except the most recent do not support subqueries.

Is there any way to achieve the result set I want without using subqueries? I need it to all be one SQL Statement.

Most likely not in one query. You could select into temporary tables, then have a single select off of that, but it still will require multiple queries not only for the select, but for the table creation.

SELECT DISTINCT item_department_id, COUNT(item_calltype) as total_item_calltype
FROM helpdesk_items
WHERE item_created >= '2006-01-01' AND item_calltype = 0
GROUP BY item_department_idEnjoy. :)

Using the same field in DISTINCT and GROUP BY is redundant. You do not need the GROUP BY clause in DISTINCT queries, if you want an order use order by instead.

From what i understand is you want the total count of each coltype.

that is a difficult query to setup correctly.

Simply upgrade to MySQL 4.1.x and enjoy the love of subselects.