Multiple COUNT(*) Queries in one Query ?

[PHPFAN10]

Hi,

 

I have created what i think is correct is one mysql_query with multiple counts. Could someone please tell me if i have done it rite ? plus how would i be able to access each count query using PHP so i can display each count query result?

 

If it's one query i know what to do but as i'm making multiple count queries in one query i don't know how to access each individual total count returned by mysql.

 

$CountQuery = mysql_query("
                    SELECT
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_COMPLETED')."' ,

                    (SELECT COUNT(*) as `referrer_uid`) 
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_DECLINED')."',
                    
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_REFERRED')."'
                    ");

 

Thanks

PHPFAN

[mikosiko]

Outch!! 

 

adjust as you want

$CountQuery = mysql_query("
                    SELECT  SUM(IF(status='REFERRAL_STATUS_COMPLETED',1,0)) AS SCOMP,
                            SUM(IF(status='REFERRAL_STATUS_DECLINED',1,0)) AS SDECL,
                            SUM(IF(status='REFERRAL_STATUS_REFERRED',1,0)) AS SREFE
                      FROM TBL_USER_REFERRALS
                      WHERE referrer_uid = $referrer_uid"); 

[Psycho]

I think you are making this more difficult than it needs to be. A simple COUNT and GROUP BY should be all you need. Try this:

$query = "SELECT `status`, COUNT(`status`) as `count`
          FROM ".constant("TBL_USER_REFERRALS")."
          WHERE `referrer_uid` = '$referrer_uid'
          GROUP BY `status`");
$result = mysql_query($query);

//Put results into array for later use
$statusCounts = array();
while($row = mysql_fetch_assoc($result))
{
    $statusCounts[$row['status']] = $row['count'];
}

 

You should have an array structure something like this:

array(
    'completed' => 12,
    'declined' => 14,
    'referrred' => 5
)

[PHPFAN10]

I think you are making this more difficult than it needs to be. A simple COUNT and GROUP BY should be all you need. Try this:

$query = "SELECT `status`, COUNT(`status`) as `count`
          FROM ".constant("TBL_USER_REFERRALS")."
          WHERE `referrer_uid` = '$referrer_uid'
          GROUP BY `status`");
$result = mysql_query($query);

//Put results into array for later use
$statusCounts = array();
while($row = mysql_fetch_assoc($result))
{
    $statusCounts[$row['status']] = $row['count'];
}

 

You should have an array structure something like this:

array(
    'completed' => 12,
    'declined' => 14,
    'referrred' => 5
)

 

Hi,

 

It prints out 214. Could you please help with that? i want to be able to access total completed, total declined and total referred counts individually but don't seem to work right as you have done it.

 

Thanks

[Psycho]

The code I provided doesn't print anything to the page. Show the code you used.

[PHPFAN10]

I just added echo before $statusCounts[$row['status']] = $row['count']; to see what it prints out and it printed out 214

 

Also i missed out another select in my original example it should have been:

 

$CountQuery = mysql_query("
                    SELECT
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid',

                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_COMPLETED')."' ,

                    (SELECT COUNT(*) as `referrer_uid`) 
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_DECLINED')."',
                    
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_REFERRED')."'
                    ");

Basically i am wanting to print out in table like example below :

 

Total Referrals 100 | Total Signups 23 (completed) | Total Declined 2 (declined) | Total Pending 3 (referred)

 

Thanks

 

[Psycho]

I provided code, you then told me the code I provided was reporting the wrong value, but then it turns out you were still using your original code. If you are going to ask for help at least try the help that is offered.

[PHPFAN10]

I provided code, you then told me the code I provided was reporting the wrong value, but then it turns out you were still using your original code. If you are going to ask for help at least try the help that is offered.

 

Hi,

 

I did try your code and just added echo before $statusCounts[$row['status']] = $row['count']; to see what it said and it said 214.

 

I then realise after making my post and after you replied that it was meant to be 4 count queries in one query but i missed out one so there was only 3.

 

So i then reposted my code with the 4 count queries in the one query to have it rectified as i made a mistake in first post.

 

When i tried your code it just echoes out 214 when i put an echo before it. As stated above i need to access each total count individually to display in table as illustrated in post above.

 

Currently i do it like this which is bad hence why i placed them all in one query but was not sure how to access each total count for each select count query.

 

        // count total referrals user has made
        $referral_total_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'");

        // count total referral signup
        $referral_total_signup_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'
            AND `status` = '".constant('REFERRAL_STATUS_COMPLETED')."' ");

        // count total referral declined
        $referral_total_declined_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'
            AND `status` = '".constant('REFERRAL_STATUS_DECLINED')."' ");

        // count total pending referrals (referred)
        $referral_total_pending_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'
            AND `status` = '".constant('REFERRAL_STATUS_REFERRED')."' ");

[Psycho]

YOU DO NOT NEED SEPARATE QUERIES. YOU ONLY NEED ONE QUERY.

 

Run this code and see what you get

$query = "SELECT `status`, COUNT(`status`) as `count`
          FROM ".constant("TBL_USER_REFERRALS")."
          WHERE `referrer_uid` = '$referrer_uid'
          GROUP BY `status`");
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    echo "{$row['status']}: {$row['count']}<br>\n";
}