Jump to content

Finding most repeated value in db


hackerkts

Recommended Posts

Sorry the title might be unclear, but I wanted to find the top few most popular sales
What I mean is, I have a table which contains all the information about the item, it has the item unique number (means 154 = item1, 155 = item2 etcs..) in that table it also contains other item information.

I have another table which records what item you bought, I wanna know how to find the top 5 most popular sales.

Example:
Ranking | Item name | Number of times bought
1. item5 50
2. item15 43
3. item3 30
4. item9 21
5. item19 5

Any helps would be appreciate, thanks.
Link to comment
https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/
Share on other sites

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] item, [color=blue]COUNT[/color](*) [color=green]AS[/color] count [color=green]FROM[/color] [color=orange]table[/color] GROUP BY item [color=green]ORDER BY[/color] count [color=green]DESC[/color] [!--sql2--][/div][!--sql3--]
Post the structure of the table that holds the data with an example of the data stored if you still have problems
Thanks for the help, but I still having problem.
Here's the table structure:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]CREATE TABLE `receiptbuy` (
`Id` char(16) NOT NULL default '',
`Prop` int(10) unsigned NOT NULL default '0',
`MoneyDelta` int(11) NOT NULL default '0',
`CashDelta` int(11) NOT NULL default '0',
`Time` datetime NOT NULL default '0000-00-00 00:00:00',
KEY `Id` (`Id`)
)[/quote]

I have do this
[code]$query = "SELECT Prop, COUNT(*) AS count FROM receiptbuy GROUP BY Prop ORDER BY count DESC LIMIT 5";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
while($row = mysql_fetch_assoc($result))
{
    echo " ".$row['Prop']." <br>";
}[/code]

Now I have top 5 best sales, but is there a way to display the total number they bought ?

Hope you know how to make it looks like the example I posted on the first thread, thanks again.
[code]
$query = "SELECT Prop, COUNT(*) AS count FROM receiptbuy GROUP BY Prop ORDER BY count DESC LIMIT 5";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
echo <<<RRR
<table>
<tr>
<th>Rank</th>
<th>item</th>
<th>num bought</th>
</tr>
RRR;
for ($i = 1; $row = mysql_fetch_assoc($result); $i++)
{
    echo "<tr><td>$i</td><td>{$row['Prop']}</td><td>{$row['count']}</td></tr>\n";
}
echo '</table>';
[/code]

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.