Mysql Query Question MySQL Select query problem

[manuraj.dhanda 0]

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.

[Eggie 0]

i dont really understand your question....maybe if you posted it more clearly i would understand

[turbopowerdmaxsteel 0]

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)

[cybernaut 0]

Try this query. I hope it is helpful.

SELECT issuedid, COUNT(status) FROM mytable GROUP BY status

[iGuest 3]

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

[kunbobo 0]

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)