Query Wanted ..

dannymm

Hi Guys,

 Can some one give me a query which will give me the below mentioned results.

i have a table with 3 columns

id,actor,patient

where actor and patient are of same type, i,e a particular value can be actor in one row and can be a patient in another row

my data :
1,A,B
2,C,D
3,B,C
4,D,E
5,B,F

now i want to get count of each actor or patient appearing in total number of rows and sort then by count i,e

output:
B,3
C,2
D,2
E,1
F,1

i,e B appears in 3 rows, C in 2 Rows and so on ....

Thanks in Advance,

Danny ...

John_Canyon

Lets assume that you want to count the "actors" and sort them by your count, from HIGH to LOW.

$sql = mysql_query("SELECT count(actor) as cnt,* FROM your_table GROUP BY actor ORDER BY cnt DESC");

This would give you the results

actor1 10
actor2 9
etc..

Sxooter

Note that any database other than MySQL will puke on that query, as you have an ungrouped return value in your select. On other databases you'll need to use a max or min to get on return, like this:

select count(field1) as cnt, max(field2) from table group by field1 order by cnt;

dannymm

Hi,

I think u guys didn't understand my problem ... it's pretty straight forward to get count and sort on a single column ...

the issue here is ....
the actor can also be a patient in some rows .. i,e if "a" is a actor in 3 columns and is also patient in another 5 columns .. then i want my results as "a" and 8 ...

your query would give me results as "a" and 5 .. since it does not check for "a" as a patient .......

Danny ..

opdsimran

I manage to build a query which gives a result not exactly that u need...

$SQL="select SUM(if((a.actor=b.patient),1,0))+1,a.actor from tbl as a ,tbl as b group by a.actor"

the above query will only list down the actor /patient name whose number of occurence is greater then 1

if you can then modify the above query to also get those name having 1 occurence..

if done reply me with the query..

Regards
OM