Jump to content

[SOLVED] Condensing 3 queries into 1 query...maybe???


Recommended Posts

Is there anyway to condense these three queries into one query? Or am I limited to running each query individually?


// to get the total number of cards in the database

$countTotalCardsStr = "SELECT COUNT(card_number) FROM user_cards";


// to get a count of all the cards that have been activated

$countActiveCardsStr = "SELECT COUNT(card_number) FROM user_cards WHERE active = 'Y'";


// to get a count of all the cards that have not been activated yet

$countInactiveCardsStr = "SELECT COUNT(card_number) FROM user_cards WHERE active = 'N'";


Thanks in advance!


Link to comment
Share on other sites

You could try:


SELECT active, count(*) FROM user_cards GROUP BY active


Is "active" not null?  If so, then the total will be the sum of Y and N.  If not, then you can't use that shortcut.


The active field is set to a 1 CHAR not null field (I use Y or N). When the card number is initially added to the database (when we first get the cards from our printer), the "active" field is set to N. Then when the customer receives card and "activates" the card, the "active" field is then set to Y. Is this a practical/clean/logical way to set up a scenario such as this? I have been using MySQL for a couple years now, but it was all self-taught, so I have a lot of the fine details to still learn.

Link to comment
Share on other sites

*slaps head* - yes of course btherl


SELECT count(*) AS total_count
     , sum(IF (active = 'Y', 1, 0)) AS total_active
     , sum(IF (active = 'N', 1, 0)) AS total_inactive
FROM card_number;


What do the 2 extra options after 'Y' or 'N' in IF (active = 'Y', 1, 0) mean?

Link to comment
Share on other sites

You can shorten them, too.  The IF() is superfluous as MySQL returns 1 on a true comparison and 0 on a false.


SELECT count(*) AS total_count
     , sum(active = 'Y') AS total_active
     , sum(active = 'N') AS total_inactive
FROM card_number;

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.