If I have a table sales:

sales {sellerid, buyerid}

I'm trying to determine the buyers favourite seller... So I need to return the most frequent sellerid.

I've used this query to find out the total number of times the buyer has bought something from any seller:

$countseller = mysql_query("SELECT count(sellerid) FROM sales WHERE buyerid = '$buid'");
		$cnt = mysql_fetch_array($countseller);
			echo "Number of times = '{$cnt[0]}'";

This returns '7' sales. So out of these 7, how can I determine which seller is the favourite?

select  sellerid, count(*) nooftimes 
from sales 
where buyerid='$buid'
group by sellerid, buyerid 
order by sellerid,buyerid,nooftimes desc 
limit 0,1

$cnt[0] will return you sellerid
$cnt[1] will return you nooftime

Hey,

This kind of works, but $cnt[0] returns the seller id that the buyer has bought from the least amount of times, rather than the most. I've tried changing 'nooftimes' to 'ASC' but it still gives me the lowest seller rather than the highest?

Member Avatar for rajarajan2017

Whether the seller id is primary key or duplicates will occur? if duplicates occur for several times for a buyer then that will be the favourite r8? if not duplicates occur then how would you said a particular as favourite? or am not clearly understand?

My mistake, here is the changed version

select  sellerid, count(*) nooftimes 
from sales 
where buyerid='$buid'
group by sellerid, buyerid 
order by buyerid,nooftimes desc, sellerid
limit 0,1

Thankyou very much

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.