5 replies to this topic

[Manu Dhanda]

Hii Guyz,

I am having this little problem creating a query in MySQL.

Problem description:

Table: mytable

Table Structure:

IssueId | ToolName | Status
01 | Tool1 | Open
02 | Tool1 | Closed
03 | Tool2 | Closed
04 | Tool3 | Open

Now, I want to get the results in the following form:

ToolName | Count(Open) | Count(Closed)

For the above input, result will be:

Tool1 | 1 | 1
Tool2 | 0 | 1
Tool3 | 1 | 0

There should be NO duplicate toolName in output.

Thnaks,
Manu.

[Eggie]

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

[turbopowerdmaxsteel]

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 by turbopowerdmaxsteel, 20 January 2008 - 03:58 PM.

[cybernaut]

Try this query. I hope it is helpful.

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

[kunbobo]

in your .php

<?php
$con = mysql_connect("hostname","user","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
//Create your database
mysql_query("CREATE DATABASE database-name ",$con);
//Create your table
mysql_select_db("database-name", $con);
$sql = "CREATE TABLE my_table

(
IssueId INT AUTO_INCREMENT PRIMARY KEY, ToolName VARCHAR(15) NOT NULL, Status VARCHAR(6)
)";
//insert
mysql_query("INSERT INTO my-table (IssueId, ToolName, Status) VALUES ('...', '...',...)")
VALUES ('...', '...',...) ... VALUES('...','...','...')");

Edited by yordan, 13 June 2012 - 01:11 PM.
added the code tag