How to make data from a column into multiple rows?

[fsqueeen]

hi, i come into a situation like this. My table is something similiar to below

table
id Name Status Datetime
1 Jack success 2006-06-06
2 Jack fail 2006-06-06
3 Jack success 2006-06-06
4 Jack success 2006-06-06
5 Mal fail 2006-06-06
6 Mal fail 2006-06-06

And i need to get the result from a specified column "Status", and the result will be something like below, which will COUNT() the no. of record. The success, fail is something calculated from "Status"

Result
Name Sucess Fail
Jack 3 1
Mal 0 2

Anybody have idea bout it?

Thanks

 

[alvechurchdata]

What tools do you have available? Access, Crystal and Fox could all do a CrossTab on that

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[fsqueeen]

Oh, i m using mysql, will it be possible?

 

[alvechurchdata]

Oh, i m using mysql, will it be possible

I've never used mySQL so I'm sorry I can't help. You might do better in a specific mySQL forum.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ceh4702]

I am thinking you use count on the two fields and use order by group by on the name. A lot depends on how many records there are. I could do this in Excel as long as the maximum number of rows is not violated. You can use a date range to limit the records. Hopefully there are not too many records.

I use Unidata DB and use a Uniquerry querry tool and it exports directly to Excel. I dont know if MySQL can do that or not. Does it come with a Querry type tool? Basically you build a querry. I dont mess with MySQL at all.

With other products you could build a view and use ODBC Drives to import the view based on a querry into an Excel Spreadsheet (or maybe some other product). Excel Prof has an Auto Filter Setting and some other tools like Pivot Table Reports. Lets you put the name rows on the vertical and the fail success fields accross the top.

If you do not like my post feel free to point out your opinion or my errors.

 

[Zathras]

I would create a temporary table to do this:
[tt]
create table #temp
(
id int,
Name varchar(30),
success int,
fail int
)
insert into #temp
select id,name,count(*),min(0)
from MyTable
where status = 'success'
group by id,name
insert into #temp
select id,name,min(0),count(*)
from MyTable
where status = 'fail'
group by id,name
select Name,sum(success) as Success,sum(fail) as Fail
from #temp
group by name
[/tt]

 

[Golom]

I think MySQL supports sub queries so look at something like

Select DISTINCT Name,

       (Select Count(*) From tbl X 
        Where  X.Name = A.Name AND Status = 'Success') As Success,

       (Select Count(*) From tbl X 
        Where  X.Name = A.Name AND Status = 'Fail') As Fail

From tbl A

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[r937]

select name
     , sum(case when status = 'success'
                then 1 
                else 0 end) as success
     , sum(case when status = 'fail'
                then 1 
                else 0 end) as fail
  from daTable
group
    by name

r937.com | rudy.ca

 

[jsteph]

You can use Crosstab to do similar to what you want, using the Status as the pivot:

TRANSFORM Count(YourTable.id) AS [The Value]
SELECT YourTable.Name, Count(YourTable.id) AS [Count status]
FROM YourTable
GROUP BY YourTable.Name
PIVOT YourTable.status;
--Jim