CODEWITHSUNDEEP
phpmysqlselectcountgroup-by
Commandrea
Commandrea

Reputation: 561

MYSQL SELECT with COUNT, FROM, WHERE, GROUP BY

I'm having trouble adding a where clause to this query. I would like it to select from where the column 'SAT AM/SUN PM' is 'yes' AND where the column 'confirmed' is 'yes'.

This works without the 'WHERE shift_times='SAT AM/SUN PM'-- it's not outputting anything with this:

        $query = "SELECT position, COUNT(confirmed) FROM volConfirm WHERE shift_times='SAT AM/SUN PM' GROUP BY position"; 

    $result = mysql_query($query) or die(mysql_error());

    // Print out result
    while($row = mysql_fetch_array($result)){
        echo "There are ". $row['COUNT(confirmed)'] ." ". $row['position'] ." " . $row['shift_times'] . " volunteers.";
        echo "<br />";

A LITTLE MORE INFO: The table contains records that are either confirmed with 'yes' or '0'(for no) and the shift_times are either SAT AM/SUN PM or SAT PM/SUN AM. There are several different positions. I am trying to display the final results like so-ish:

There are: "30" "Art" volunteers for "SAT AM/SUN PM"

There are: "30" "Art" volunteers for "SAT PM/SUN AM"

Ideally the rows would rotate so the echo under that would be the inverse data for "SAT PM/SUN AM"- but that seems a bit trickier...

Upvotes: 0

Views: 12428

Answers (1)

Michael Blood
Michael Blood

Reputation: 1267

I changed your select statement to select and group by the shift times, so one row would be selected per position, per shift time. I added an alias of 'cnt' to your count() and updated the php to use cnt in the echo statement No closing bracket at the end of your while loop (could be a copy and paste issue)

$query = "SELECT COUNT(confirmed) as cnt
          , position
          , shift_times 
         FROM volConfirm 
         WHERE confirmed='yes'  
         GROUP BY shift_times, position
         order by shift_times, position"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result))
{
    echo "There are ". $row['cnt'] ." ". $row['position'] ." " . $row['shift_times'] . " volunteers.";
    echo "<br />";
}

Upvotes: 3

Related Questions