CODEWITHSUNDEEP
phpmysqlcountfrequency
Cody Raspien
Cody Raspien

Reputation: 1835

Count of a count- PHP MYSQL

My table is as follows:

 guid version

 A1   lol
 A2   roflmao
 A1   lol
 A1   lol
 A2   lol
 A3   roflmao
 A3   roflmao
 A3   roflmao
 A3   roflmao 
 A3   roflmao
 A3   roflmao
 A4   roflmao
 A4   roflmao
 A5   lol   
 A5   lol

I want to count then number of times roflmao appear under 3 times (per GUID) in total.

So far I've the following but it's not outputting anything:

$sql="select guid,version, count(*) as `count` 
from mytable WHERE version = 'roflmao' group by guid having `count` < 3'";

if ($result=mysqli_query($con,$sql))
{

 // Return the number of rows in result set

 $rowcount=mysqli_num_rows($result);
 printf($rowcount);

 // Free result set

 mysqli_free_result($result);
 }

Upvotes: 0

Views: 32

Answers (1)

sagi
sagi

Reputation: 40481

Try this:

SELECT COUNT(*) FROM (
    SELECT guid
    FROM YourTable
    WHERE version = 'roflmao'
    GROUP BY guid
    HAVING COUNT(*) <3) t

This first select all the guids that has 'roflmao' appearing less them 3 times, and then counts the them.

Upvotes: 2

Related Questions