Mysql – Aggregate row sum two columns on table from same key

MySQL

I have a table named inward_outward where I want to group by name and show total entries, total inward count and total outward count.

This is what I have so far

include('includes/dbcon.php');

    $query=mysqli_query
    ($con," SELECT name from inward_outward,
    ( SELECT  count(status) from inward_outward WHERE status ='inward' )As count_1,
    ( SELECT count(status) from inward_outward WHERE status ='out' )As count_2,
    ( SELECT count(status) from inward_outward WHERE 1 )As count_3
    GROUP BY name")

    or die(mysqli_error($con));
      while ($row=mysqli_fetch_array($query)){


    echo "<tr>";    
echo "<td>" . $row['inspector_name'] . "</td>";
echo "<td>" . $row['count_1'] . "</td>";
echo "<td>" . $row['count_2'] . "</td>";
echo "<td>" . $row['count_3'] . "</td>";
echo "</tr>";
      }

I want to take count from status column where status = 'inward' and status = 'out' then group by name

Best Answer

I'm unclear on what you want, but I think this will come closer:

SELECT name,
       SUM(status = 'inward') AS in_ct,
       SUM(status = 'out')    AS out_ct,
       COUNT(*)               AS both
    FROM inward_outward
    GROUP BY name;

Notes:

  • SUM(boolean-expr) counts the number of TRUEs
  • COUNT(*) counts rows; COUNT(x) counts the number of non-null x
  • GROUP BY should list all the non-aggregates, and nothing else.
  • Subqueries are not needed in this case.
  • FROM must come after the items selected.