0

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?

Edited by nonshatter: n/a

3
Contributors
5
Replies
7
Views
7 Years
Discussion Span
Last Post by nonshatter
0
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

0

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?

0

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?

Edited by rajarajan07: n/a

0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.