Aggregate row sum two columns on table from same key

2018-10-19 05:30:52

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 "";

echo "" . $row['inspector_name'] . "";

echo "" . $row['count_1'] . "";

echo "" . $row['count_2'] . "";

echo "" . $row['count_3'] . "";

echo "";

}

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

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

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

    2018-10-19 05:54:31