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.

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

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.



For inserting values use queries similar to:-



0 is equivalent to Closed and 1 to Open

To retrieve your result use the following query:-



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



Data Insertion



Data Output



This post has been edited by turbopowerdmaxsteel: Jan 20 2008, 03:58 PM

Try this query. I hope it is helpful.