COUNT(DISTINCT) help plz.

[bazz]

ok, I am querying a table where there are three cols. I need the distinct values from each column, relative to the preceding one.

code so far:


my $sth = $dbh->prepare ("SELECT COUNT(DISTINCT Business_Type),COUNT(DISTINCT Business_Sub_Type),COUNT(DISTINCT Business_Category)
FROM $table BT
WHERE Business_Type = '$parameter'
ORDER BY Business_Type,Business_Sub_Type,Business_Category
") or die "prepare statement failed: $DBO::errstr\n";

my $rc = $sth->execute
or die "Can't execute statement: $DBI::errstr";



This gives me the total number of distinct values in each field however, I need to retrieve the total number of distinct values in col2, for each distinct value in col 1. likewise for col 3, I need the number of distinct values from col3, for each of col 2 and col1's distinct values.

do I need to start building sub queries or what is the appropriate step forward.

bazz

[oesxyl]

If I understand what you want you could do:


select distinct Bussines_Type, ( select count(distinct Bussines_Category) from table BT group by Bussines_Type) as category, (select count( distinct Bussines_Sub_Type) from BT group by Bussines_Category) as sub_category from BT order by Bussines_Type


This will give you three columns, Bussines_Type, category, sub_category and the results will be, I hope, :), the enumeration of Bussines_Type in first column, and how meny Bussines_Category and Bussines_Sub_Category for each in the rest of columns

I can't test it I hope it work as is. Anyway if something go wrong post results.


I'm sorry, I find a table with a similar structure I tested and don't work. Give a error in mysql, 1242: "subquery returns more then one rows"

I'll try another way.


best regards

[bazz]

I got it working a different way, with help from FishMonger, following on from a much earlier thread.

Thank you oesxyl for your time.

bazz

[StupidRalph]

C'mon Bazz you know I usually find all of my answers to my problems by viewing your threads...share the wealth :P.

[bazz]

lol,


my $sth = $dbh->prepare ("SELECT Business_Type, Business_Sub_Type, Business_Category
FROM $table BT
WHERE Business_Type = '$parameter'
ORDER BY Business_Type,Business_Sub_Type,Business_Category
") or die "prepare statement failed: $DBO::errstr\n";

my $rc = $sth->execute;

my %entered_Business_Data;


while ( my @cols = $sth->fetchrow_array) {
$entered_Business_Data{$cols[0]}{$cols[1]}{$cols[2]}++;
}


It's probably different for php but, I don't know where or how it is different. I use perl. :)

bazz