I'm trying to build a query to show counts of specific columns by value. There's probably a simple way to do this that I don't know about, and if you could just point me to the function or part of the manual to research before you start laughing, I'd greatly appreciate it.
Given the following table: +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | questnum | int(11) | | PRI | NULL | auto_increment | | class | varchar(32) | YES | | NULL | | | difficulty | tinyint(4) | YES | | NULL | | | in_use | tinyint(4) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+
Where difficulty is 0, 1, or 2 (easy, medium, hard) and in_use is 0 or 1 (quiz or exam). Given the following example data:
mysql> select * from Questions; +----------+---------------+------------+--------+ | questnum | class | difficulty | in_use | +----------+---------------+------------+--------+ | 1 | algebra | 0 | 0 | | 2 | discrete math | 0 | 0 | | 3 | geometry | 1 | 0 | | 4 | trigonometry | 1 | 1 | | 5 | calculus | 2 | 1 | | 6 | hypermath | 2 | 0 | | 7 | calculus | 1 | 0 | | 8 | discrete math | 2 | 0 | | 9 | algebra | 0 | 1 | | 10 | trigonometry | 0 | 1 | | 11 | calculus | 2 | 0 | +----------+---------------+------------+--------+
What I want to do is query and get a result that I can display like this (probably irrelevant, but the application and presentation layer is handled in Perl with DBI and DBIx::XHTML_Table):
+---------------+-------+------+------+------+------+------+ | Class | Total | Easy | Med. | Hard | Quiz | Exam | +---------------+-------+------+------+------+------+------+ | algebra | 2 | 2 | | | 1 | 1 | | calculus | 3 | | 1 | 2 | 2 | 1 | | discrete math | 2 | 1 | | 1 | 2 | | | geometry | 1 | | 1 | | 1 | | | hypermath | 1 | | | 1 | 1 | | | trigonometry | 2 | 1 | 1 | | | 2 | +---------------+-------+------+------+------+------+------+
Obviously I can do this with 6 queries, but that seems inefficient:
mysql> select class,count(questnum) from Questions group by class;
mysql> select class,count(difficulty) from Questions where difficulty = '0' group by class;
mysql> select class,count(difficulty) from Questions where difficulty = '1' group by class;
mysql> select class,count(difficulty) from Questions where difficulty = '2' group by class;
mysql> select class,count(in_use) from Questions where in_use = '0' group by class;
mysql> select class,count(in_use) from Questions where in_use = '1' group by class;
I tried (you can laugh here) to do it this way, but failed miserably:
mysql> select class,count(questnum),count(difficulty='0'), count(difficulty='1'),count(difficulty='2'), count(in_use='0'),count(in_use='1') from Questions group by class;
Is there an elegant way to do this, or should I just make a bunch of different queries and glue it all together on the other side?
Any help is appreciated. I'm decent at basic SQL but this is pushing into an area I'm unfamiliar with, so if you can just point me toward the answer I can probably teach myself, but I need a little help to get there.
--
gowen -- Greg Owen -- [EMAIL PROTECTED]
79A7 4063 96B6 9974 86CA 3BEF 521C 860F 5A93 D66D
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
