Retriving data in single select statement

[barbara1712]

Hello

I have mysql table with the following fields name, status, address.
But status field has values open and closed. But I want a select statement that will select name status for records opened, then status for records closed using single select statement.

Meaning it should count number of records that are closed, then opened and group them by class.

meaning if it class A has 6 records open and 5 , and if class has 3 records closed and 5 opened. it should display them as follows;


Class Closed Opened
A 6 5

B 3 5

Your help will be highly appreciated.

[POS]

Apologies for possible syntax errors. Never used MySql before.

There must be an easy way to combine two aggregrate results in a cross query.

I await to see of anyone replies with a more efficient solution.

Anyway hope this helps for the time being.


CREATE VIEW RECORDS_OPEN
AS (SELECT name,Status
FROM your_table
WHERE Status="open");

CREATE VIEW RECORDS_CLOSED
AS (SELECT name,Status
FROM your_table
WHERE Status="closed");

CREATE VIEW RECORDS_CLOSED_COUNT
AS (SELECT name, Count(Status) AS ClosedStatus
FROM RECORDS_OPEN
GROUP BY name);

CREATE VIEW RECORDS_OPEN_COUNT
AS (SELECT name, Count(Status) AS OpenStatus
FROM RECORDS_CLOSED
GROUP BY name);

AND FINALLY....THE BIT YOU WANT TO SEE

SELECT ROC.name, ROC.OpenStatus,RCC.ClosedStatus
FROM RECORDS_OPEN_COUNT ROC ,RECORDS_CLOSED_COUNT RCC
WHERE ROC.name = RCC.name;

[sylwesters]

Hi you could try something like this:

select name,
count(case when status='closed' then 1 else 0 end) as Closed,
count(case when status='open' then 1 else 0 end) as Open
from
tablename
group by name

Hope it will help,
S.