manuraj.dhanda 0 Report post Posted January 20, 2008 Hii Guyz,I am having this little problem creating a query in MySQL.Problem description:Table: mytableTable Structure:IssueId | ToolName | Status01 | Tool1 | Open02 | Tool1 | Closed03 | Tool2 | Closed04 | Tool3 | OpenNow, I want to get the results in the following form:ToolName | Count(Open) | Count(Closed)For the above input, result will be:Tool1 | 1 | 1Tool2 | 0 | 1Tool3 | 1 | 0There should be NO duplicate toolName in output.Thnaks,Manu. Share this post Link to post Share on other sites
Eggie 0 Report post Posted January 20, 2008 i dont really understand your question....maybe if you posted it more clearly i would understand Share this post Link to post Share on other sites
turbopowerdmaxsteel 0 Report post Posted January 20, 2008 (edited) Use the following code to create the Table structure. It is better to create the Status field as bit which can have only two values/states 0 and 1. CREATE TABLE mytable(IssueId INT AUTO_INCREMENT PRIMARY KEY,ToolName VARCHAR(15) NOT NULL,Status BIT) For inserting values use queries similar to:- INSERT INTO mytable VALUES('', 'Tool1', 1);INSERT INTO mytable VALUES('', 'Tool1', 0);INSERT INTO mytable VALUES('', 'Tool2', 0);INSERT INTO mytable VALUES('', 'Tool3', 1); 0 is equivalent to Closed and 1 to Open To retrieve your result use the following query:- SELECT DISTINCT ToolName, (SELECT COUNT(Status) FROM mytable t2 WHERE Status = '1' AND t1.ToolName = t2.ToolName), (SELECT COUNT(Status) FROM mytable t2 WHERE Status = '0' AND t1.ToolName = t2.ToolName) FROM mytable t1 It contains two subqueries to do the job. Given below are codes for implementing the Status as a normal VARCHAR but I would recommend using the BIT data type. Table Structure CREATE TABLE mytable(IssueId INT AUTO_INCREMENT PRIMARY KEY,ToolName VARCHAR(15) NOT NULL,Status VARCHAR(6)) Data Insertion INSERT INTO mytable VALUES('', 'Tool1', 'Open');INSERT INTO mytable VALUES('', 'Tool1', 'Closed');INSERT INTO mytable VALUES('', 'Tool2', 'Closed');INSERT INTO mytable VALUES('', 'Tool3', 'Open'); Data Output SELECT DISTINCT ToolName, (SELECT COUNT(Status) FROM mytable t2 WHERE Status = 'Open' AND t1.ToolName = t2.ToolName), (SELECT COUNT(Status) FROM mytable t2 WHERE Status = 'Closed' AND t1.ToolName = t2.ToolName) FROM mytable t1 Edited January 20, 2008 by turbopowerdmaxsteel (see edit history) Share this post Link to post Share on other sites
cybernaut 0 Report post Posted February 15, 2008 Try this query. I hope it is helpful. SELECT issuedid, COUNT(status) FROM mytable GROUP BY status Share this post Link to post Share on other sites
iGuest 3 Report post Posted April 9, 2012 SELECT `ToolName`, SUM(Status=1), SUM(Status=0) FROM `tools` GROUP BY `ToolName` ORDER BY `ToolName` ASCI find this as easier than using sub queries Share this post Link to post Share on other sites
kunbobo 0 Report post Posted June 13, 2012 (edited) in your .php <?php$con = mysql_connect("hostname","user","pass");if (!$con) { die('Could not connect: ' . mysql_error()); }//Create your databasemysql_query("CREATE DATABASE database-name ",$con);//Create your tablemysql_select_db("database-name", $con);$sql = "CREATE TABLE my_table(IssueId INT AUTO_INCREMENT PRIMARY KEY, ToolName VARCHAR(15) NOT NULL, Status VARCHAR(6))";//insertmysql_query("INSERT INTO my-table (IssueId, ToolName, Status) VALUES ('...', '...',...)")VALUES ('...', '...',...) ... VALUES('...','...','...')"); Edited June 13, 2012 by yordan added the code tag (see edit history) Share this post Link to post Share on other sites