Jump to content

Archived

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

hackerkts

Finding most repeated value in db

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.

Share this post


Link to post
Share on other sites
Bump..
No one help me yet >.<!

Actually it's like IPB "Most active in <category>"

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
[code]<?php
echo "OMG shoz you rocks !!!";
unlink("http://www.phpfreaks.com/forums/index.php?showtopic=96245");
?>[/code]

:P Thanks.

Share this post


Link to post
Share on other sites

×

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.