Multiple Count from same table

Question

I used to get multiple COUNT in one query using the SUM(CASE WHEN

$db =& JFactory::getDBO();
$query = "SELECT
        SUM(CASE WHEN last_name LIKE 'A%' THEN 1 ELSE 0 END) as alpha,
        SUM(CASE WHEN last_name LIKE 'B%' THEN 1 ELSE 0 END) as beta
        FROM '#__mytable'"; 
$db->setQuery($query);
$results = $db->query();
while($row = mysqli_fetch_array($results)){
    $alpha = $row['alpha'];
    $beta = $row['beta'];
}
echo "ALPHA: ".$alpha;
echo "<br />BETA: ".$beta;

Is there a way to do it in one query with JDatabase and avoid multiple single queries:

$db =& JFactory::getDBO();
$query = $db->getQuery(true);
$query->select( array('COUNT(id)'))->from($db->quoteName('#__mytable'))->where($db->quoteName('last_name') . ' LIKE ' .$db->quote('A%'));
$db->setQuery($query);
$alpha = $db->loadResult();

$query = $db->getQuery(true);
$query->select( array('COUNT(id)'))->from($db->quoteName('#__mytable'))->where($db->quoteName('last_name') . ' LIKE ' .$db->quote('B%'));
$db->setQuery($query);
$beta = $db->loadResult();

echo "ALPHA: ".$alpha;
echo "<br />BETA: ".$beta;

If not what would be the best approach for multiple COUNT in one query?

Answer 1

You can shorten, and reuse the query:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('COUNT(id)'))
    ->from($db->qn('#__mytable'))
    ->where($db->qn('last_name') . ' LIKE ' . $db->q('A%'));
$db->setQuery($query);
$alpha = $db->loadResult();
// you can reuse the object, but not the where-clause:
$query->clear('where')
    ->where($db->qn('last_name') . ' LIKE ' . $db->q('B%'));
$db->setQuery($query);
$beta = $db->loadResult();
echo 'ALPHA: '.$alpha;
echo '<br />BETA: '.$beta;

I think this is a good compromise. And your single query IMHO is not really a cheap procedure compared to this.

Also some hints:

• In the first line, you should not use the & any more at JFactory::getDbo().

• Use simply ' instead of " for any string operations, then there is no extra parsing through the string if possible.

• You may use $db->qn() instead of $db->quoteName() and $db->q() for $db->Quote()