From: Greg Owen [mailto:[EMAIL PROTECTED] > 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.
Actually, this was a welcome distraction. :) > 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 | > +---------------+-------+------+------+------+------+------+ Here's the shorter version, which puts in 0s instead of the blanks in your table: SELECT class AS Class, COUNT(Class) AS Total, SUM(IF(difficulty=0,1,0)) AS Easy, SUM(IF(difficulty=1,1,0)) AS `Med.`, SUM(IF(difficulty=2,1,0)) AS Hard, SUM(IF(in_use=0,1,0)) AS Quiz, SUM(IF(in_use=1,1,0)) AS Exam FROM Questions GROUP BY Class ORDER BY Class; And the following will do the same, but with blanks instead of 0s (as you had above): SELECT class AS Class, COUNT(Class) AS Total, IF(SUM(IF(difficulty=0,1,0))=0,'',SUM(IF(difficulty=0,1,0))) AS Easy, IF(SUM(IF(difficulty=1,1,0))=0,'',SUM(IF(difficulty=1,1,0))) AS `Med.`, IF(SUM(IF(difficulty=2,1,0))=0,'',SUM(IF(difficulty=2,1,0))) AS Hard, IF(SUM(IF(in_use=0,1,0))=0,'',SUM(IF(in_use=0,1,0))) AS Quiz, IF(SUM(IF(in_use=1,1,0))=0,'',SUM(IF(in_use=1,1,0))) AS Exam FROM Questions GROUP BY Class ORDER BY Class; The key parts being used are the SUM() function and IF() statements. SUM() simply totals what you're giving it. The structure of IF() is IF(condition, output if condition is true, output if condition is false). Using an IF() statement, you can control exactly what gets sent to SUM(). The lines for each of the right 5 columns are just that. For the 'Easy' column, if difficulty=0 send 1 to SUM(), else send 0. And so on. My apologies if my SQL is a bit compacted or tough to read. It makes sense to me because I wrote it piece-by-piece, but I imagine it doesn't look so simple to someone else. :) I'm not sure of any standards for nested functions and control structures such as that. Anyone have any input on that? -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
