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?