How to get the number of occurrences of each distinct GROUP CONCAT value (php, mysql)
I know this is very easy to do, but I haven't gotten the right way to do it. i found something here but different from what i need and no active input yet. Someone will kindly help.
I have a table like this
name |status
-------------
mike |yes
mike |yes
mike |no
mike |ney
john |no
john |ney
john |yes
I want to output something like this
name |status |total
------------------------------
mike |yes-2,no-1,ney-1 | 4
john |yes-1,no-1,ney-1 | 3
I tried to use GROUP_CONCAT like
result = mysql_query("SELECT name, GROUP_CONCAT(DISTINCT status) AS status
FROM table GROUP BY name ");
while($row = mysql_fetch_array($result)){
$st[] = $row['status'];
$status=explode(",",$row['status']);
$total = count($status);
echo $row['name']."|".$row['status']."|".$total."<br><br>"; }
I would like to get the number of each individual $row['status']
and, if possible, the best way to get to $total
.
EDIT1
name | yes | no | ney | total
------------------------------
mike |2 |1 |1 | 4
john |1 |1 |1 | 3
This second result was achieved here
source to share
There is no need to use php as you can use pure SQL to get your desired result set:
SELECT name, GROUP_CONCAT(totalPerStatus) AS status,
(SELECT COUNT(*) FROM mytable WHERE name = t.name) AS total
FROM (
SELECT name,
CONCAT(status, '-', COUNT(*)) AS totalPerStatus
FROM mytable
GROUP BY name, status ) t
GROUP BY name;
Using grouping name, status
in a subquery gives you the "status" bill for "name". Using CONCAT
, you get the following set of results:
name totalPerStatus
-----------------------
john ney-1
john no-1
john yes-1
mike ney-1
mike no-1
mike yes-2
The outer query uses GROUP_CONCAT
on totalPerStatus
to create the required result set.
source to share