bookmark - Mysql Query Question MySQL Select query problem

Mysql Query Question - MySQL Select query problem

 
 Discussion by Manu Dhanda with 5 Replies.
 Last Update: June 13, 2012, 8:53 am
 
bookmark - Mysql Query Question MySQL Select query problem  
    
free web hosting
 
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.

Sun Jan 20, 2008    Reply    New Discussion   


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

Sun Jan 20, 2008    Reply    New Discussion   

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.

CODE

CREATE TABLE mytable
(
IssueId INT AUTO_INCREMENT PRIMARY KEY,
ToolName VARCHAR(15) NOT NULL,
Status BIT
)


For inserting values use queries similar to:-

CODE

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:-

CODE

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

CODE

CREATE TABLE mytable
(
IssueId INT AUTO_INCREMENT PRIMARY KEY,
ToolName VARCHAR(15) NOT NULL,
Status VARCHAR(6)
)


Data Insertion

CODE

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

CODE

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

Sun Jan 20, 2008    Reply    New Discussion   

Try this query. I hope it is helpful.

QUOTE

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

Fri Feb 15, 2008    Reply    New Discussion   


SELECT `ToolName`, SUM(Status=1), SUM(Status=0) FROM `tools` GROUP BY `ToolName` ORDER BY `ToolName` ASC
I find this as easier than using sub queries

Mon Apr 9, 2012    Reply    New Discussion   

in your .php

CODE

<?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('...','...','...')");

Wed Jun 13, 2012    Reply    New Discussion   

Quickly Post to Mysql Query Question MySQL Select query problem w/o signup Share Info about Mysql Query Question MySQL Select query problem using Facebook, Twitter etc. email your friend about Mysql Query Question MySQL Select query problem Print
Reply / Comment Ask a Question? Share / Bookmark E-Mail a Friend Print

Need Help In Database Auto_increment   Need Help In Database Auto_increment (9) (3) Lost Connection To Mysql ,..., System Error: 111 In   Lost Connection To Mysql ,..., System Error: 111 In