i want to count multiple values from 3 columns and sorted descending using PHP mysqli, and I can't do it.
| name1 | name2 | name3 |
|---|---|---|
| mike | jack | rose |
| jack | mike | Mary |
| jack | Mary | John |
expect output:
jack 3
mike 2
mary 2
rose 1
John 1
My code:
$query = "select count(*) as c from work" ;
$result = $con->query($query);
$count = $result->fetch_object()->c;
Use UNION to get them all into a single column. Then use COUNT(*) and GROUP BY:
$query = "
SELECT name, COUNT(*) AS c
FROM (
SELECT name1 AS name
FROM work
UNION ALL
SELECT name2 AS name
FROM work
UNION ALL
SELECT name3 AS name
FROM work
) AS x
GROUP BY name
ORDER BY c DESC";
$result = $con->query($query);
while ($row = $result->fetch_object()) {
echo "$row->name $row->c<br>";
}
