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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.