count() by different groupings

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

count() by different groupings

Post by wanger220 »

Hey all, been researching this for a few hours now but haven't come up with a decent solution. Would appreciate it if someone could point me in the right direction..

I have a query that counts the number of available apartments based on size. However, some of those units are also penthouses, and I need it to return a separate count for the total number of penthouses, regardless of size.

Is there a way to do this without writing a second query? I tried to include the penthouse column in the query, but as I expected, it does not return the correct number since the data is already grouped by size. Suggestions??
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: count() by different groupings

Post by requinix »

Multiple GROUP BYs. What's your query?
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

Re: count() by different groupings

Post by wanger220 »

I didn't think that would work. Here is what I tried:

Code: Select all

 
$query = "SELECT rooms,ph,COUNT(1) as unitcount FROM table WHERE status='1' GROUP BY rooms,ph";
$result = mysql_query($query) or die();
 
while ($row = mysql_fetch_array($result)) {
if ($row['rooms'] == '1') { $onebed = $row['unitcount']; }
...
if ($row['ph'] == 'Y') { $ph = $row['unitcount']; }
}
 
This didn't work, and I knew that, since the table is already grouped by rooms. But I know there must be a solution and I'll probably feel stupid once I figure it out.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: count() by different groupings

Post by requinix »

Do you find yourself looking at penthouses separately from normal apartments often? Do you need the number of rooms for a penthouse?
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

Re: count() by different groupings

Post by wanger220 »

It's supposed to be a table summarizing available units: number of studios, 1BR, 2BR, 3BR, 4BR, as well as Penthouses (which range from 2BR-4BR and are included in the other categories). In the database, each unit has a field listing the number of rooms and another designating penthouse Y/N.

In the interest of time, I just wrote three separate queries to do everything I need, but I'm wondering if there is a more efficient method.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: count() by different groupings

Post by requinix »

There could be, but I'm thinking that separate queries make more sense than trying to do it all at once.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: count() by different groupings

Post by VladSun »

tasairis++

I think, even using a UNION should be considered a "separate" query ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply