I have 2 tables, category and ticket (relevant description follows): mysql> describe ticket; +-----------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------------------+----------------+ | id | int(6) unsigned | | PRI | NULL | auto_increment | | catid | int(6) unsigned | | | 0 | | | state | tinyint(3) unsigned | YES | | 0 | | +-----------------+---------------------+------+-----+---------------------+----------------+ mysql> describe category; +-------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------+------+-----+---------+----------------+ | id | int(6) unsigned | | PRI | NULL | auto_increment | | name | char(128) | | | | | +-------+-----------------+------+-----+---------+----------------+
ticket.catid := SELECT id FROM category; I'm trying to formulate a single SELECT query (possible subqueries) to return the following data: category.id AS catid, category.name AS catname, (SELECT catid, COUNT(catid) AS tick_total FROM ticket GROUP BY catid), (SELECT state, count(state) AS state_count FROM ticket WHERE catid=X GROUP BY state) Basically: - category id and name - total number of tickets in the category - total number of tickets in each state per category. Row would look like: catname | catid | tick_total | state_0_count | state_1_count | ... | state_n_count Is this possible using queries and subqueries? Or would this be something I'd have to use procedures for? (I'm working on 4.1 so no procedures, which is why I ask.) I've been trying to find a way to do it with subqueries but the roadblock I seem to be hitting is I need some way to turn the fields of a row from one query into the columns of another query. Note, I know this could be easily accomplished in the program instead of MySQL, but it's always a challenge to see how much I can stuff into a single query. -- Ryan Sommers [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
