buxbeatz buxbeatz - 4 months ago 6
PHP Question

Multiple count queries with same grouping

I am trying to retrieve the count of different values with different WHERE in the queries but have them all grouped by the same value.

I tried with following example:

$sql = "SELECT * FROM ( SELECT COUNT(id) AS mail FROM leadz WHERE source = 'mail' UNION ALL
SELECT COUNT(id) AS phone FROM leadz WHERE source = 'phone' UNION ALL
SELECT COUNT(id) AS direct FROM leadz WHERE source = 'direct' UNION ALL
SELECT COUNT(id) AS external FROM leadz WHERE source = 'external'
) GROUP BY date";


Then I try to access the data by using:

$query = mysqli_query($con,$sql);
while($row = mysqli_fetch_assoc($query)){
echo "in date ".$row['date'].": ".$row['mail'].",".$row['phone'].",".$row['direct'].",".$row['external']."<br>";


This obviously is not working. Anyone help appreciated.

Answer

You could try something where instead of separate sub-queries you use a CASE statement with a sum thusly:

Original Code:

$sql = "SELECT * FROM (  SELECT COUNT(id) AS mail FROM leadz WHERE source = 'mail' UNION ALL
             SELECT COUNT(id) AS phone FROM leadz WHERE source = 'phone' UNION ALL
             SELECT COUNT(id) AS direct  FROM leadz WHERE source = 'direct' UNION ALL 
             SELECT COUNT(id) AS external FROM leadz WHERE source = 'external' 
                   ) GROUP BY date";

Updated using a sum of with conditions:

$sql = "SELECT
l.DATE
,SUM(CASE WHEN l.SOURCE = 'mail' THEN 1 ELSE 0 END) AS SOURCE_MAIL
,SUM(CASE WHEN l.SOURCE = 'phone' THEN 1 ELSE 0 END) AS SOURCE_PHONE
,SUM(CASE WHEN l.SOURCE = 'direct' THEN 1 ELSE 0 END) AS SOURCE_DIRECT
,SUM(CASE WHEN l.SOURCE = 'external' THEN 1 ELSE 0 END) AS SOURCE_EXTERNAL
FROM LEADZ AS l
GROUP BY l.DATE";

Then to access the output in php you could use something like this:

$result = mysqli_query($link,$sql);
while($r = mysqli_fetch_array($result)) {
extract($r);
echo $DATE.'<BR>'.
'mail: '$SOURCE_MAIL.'<BR>'.
'phone: '$SOURCE_PHONE.'<BR>'.
'direct: '$SOURCE_DIRECT.'<BR>'.
'external: '$SOURCE_EXTERNAL.'<BR><hr>';
}
Comments