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

[dshevnock]

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!

 

[bubblegum.anarchy]

Probably not quite what you are asking for but:

 

SELECT 
( SELECT count(card_number) FROM user_cards ) AS card_number_total,
( SELECT count(card_number) FROM user_cards WHERE active = 'Y' ) AS card_number_active,
( SELECT count(card_number) FROM user_cards WHERE active = 'N' ) AS card_number_inactive

[btherl]

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.

[bubblegum.anarchy]

*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;

[dshevnock]

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.

[dshevnock]

*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?

[Illusion]

If it is true it returns 1 so the SUM function counts all the return 1's for active=y so that is the count for active members , similarly for inactive members.

[Wildbug]

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;

[dshevnock]

Awesome! Thanks everyone. You all have been a huge help!