Multiple COUNT(*) Queries in one Query ?

Question

phplover 0 Junior Poster in Training

13 Years Ago

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
PHPLOVER

php

Edited 13 Years Ago by phplover because: n/a

3 Contributors
10 Replies
1K Views
22 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by phplover

minitauros 151 Junior Poster

13 Years Ago

Well, I don't think there's much difference between executing 4 different queries in one query and executing 4 different queries apart from eachother.

I've never tried it, but I don't think executing 4 counts in one query will work. Besides that, COUNT just counts the rows that are selected, so if your first query counts 3 rows, and the second one counts 6 rows, the result will be 6, not both 3 and 6 (for as far as I know).

So I'd say: just execute the 4 queries apart from eachother. That'll work for sure.

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

$result=mysql_query("SELECT count(*) total
, sum(if(`status` = '".constant('REFERRAL_STATUS_COMPLETED')."',1,0)) com_count
, sum(if(`status` = '".constant('REFERRAL_STATUS_DECLINED')."',1,0)) dec_count,
, sum(if(`status` = '".constant('REFERRAL_STATUS_REFERRED')."',1,0)) ref_count
FROM ".constant("TBL_USER_REFERRALS")." where `referrer_uid`='$referrer_uid' ");

Edited 13 Years Ago by urtrivedi because: n/a

phplover commented: Works Perfectly Thank you! :) +1

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

there is extra comma after dec_count,

it should look like dec_count (remove comma)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

phplover 0 Junior Poster in Training · Answer 1 · 2011-04-20T04:40:17+00:00

Sorry code was meant to be:

$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')."'
                    ");

I want to access each total count so i can use in a table something like

Total Referalls xx (1st select count query in above code) | Total Signups xx (completed) | Total Declined xx (declined) | Total Pending xx (referred)

Thanks

(where xx is that would be the total count for each select count(*) )

phplover 0 Junior Poster in Training · Answer 2 · 2011-04-20T15:54:14+00:00

Hi,

I know i could improve the query but not sure how plus not sure how i would access each individual select count(8) result. So if anyone knows please reply?

Thanks

urtrivedi 276 Nearly a Posting Virtuoso · Answer 3 · 2011-04-20T16:03:10+00:00

$result=mysql_query("SELECT count(*) total, sum(if(`referrer_uid`='abc',1,0) abc_count,
, sum(if(`referrer_uid`='pqr',1,0) pqr_count,
, sum(if(`referrer_uid`='xyz',1,0) xyz_count
FROM ".constant("TBL_USER_REFERRALS"));

output

total, abc_count, pqr_count, xyz_count
45, 10, 15, 20

You can access result as you access any other mysql result by using column name

phplover 0 Junior Poster in Training · Answer 4 · 2011-04-20T16:21:47+00:00

Hi,

I tried to access the individually results and echo them out but don't get any errors or results. I have all PHP errors on and to display them.

Maybe i am doing it wrong ?

$sql=mysql_query("
            SELECT count(*) total, sum(if(`referrer_uid`='$referrer_uid',1,0) abc_count,
            , sum(if(`referrer_uid`='$referrer_uid',1,0) pqr_count,
            , sum(if(`referrer_uid`='$referrer_uid',1,0) xyz_count
            FROM ".constant("TBL_USER_REFERRALS"));
           
            while ($row = mysql_fetch_array($sql)){
                echo $row['abc_count'];
                echo $row['xyz_count'];
                echo $row['pgr_count'];
            }

Thanks for any help and sorry if i am getting it wrong.

The only difference between each select count(*) query in my original code is that the status i am checking for is different.

total referrals (1st query in my original code) | completed (2nd query in original code) | declined ( 3rd query in original code) | referred (4th query in original code)

This is how i can then display like total referrals, total completed referrals, total declines referrals and so on.

Currently i do it like this which is tedious as you could imagine:

// 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')."' ");

urtrivedi 276 Nearly a Posting Virtuoso · Answer 5 · 2011-04-20T16:35:30+00:00

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

post your mysql table script with some sample data of all four types

Edited 13 Years Ago by urtrivedi because: n/a

phplover 0 Junior Poster in Training · Answer 6 · 2011-04-20T17:37:54+00:00

Hi,

Using this code you gave (last code suggestion you made)

$sql=mysql_query("SELECT count(*) total
            , sum(if(`status` = '".constant('REFERRAL_STATUS_COMPLETED')."',1,0)) com_count
            , sum(if(`status` = '".constant('REFERRAL_STATUS_DECLINED')."',1,0)) dec_count,
            , sum(if(`status` = '".constant('REFERRAL_STATUS_REFERRED')."',1,0)) ref_count
            FROM '".constant("TBL_USER_REFERRALS")."' WHERE `referrer_uid` ='$referrer_uid' ") or die(mysql_error());

I get this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' sum(if(`status` = 'referred',1,0)) ref_count FROM user_referrals wh' at line 4

My database is like shown in image below:

http://img820.imageshack.us/f/sampledb.jpg/

It should print out the following which is correct based on referred_uid 806

http://img600.imageshack.us/f/sampledbresults.jpg/

Thanks for all your help!

phplover 0 Junior Poster in Training · Answer 7 · 2011-04-20T18:06:47+00:00

Thank you! :) It works great.

Much appreciate all your time and effort in helping me :)