Page 1 of 1

count() by different groupings

Posted: Fri Mar 05, 2010 1:02 pm
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??

Re: count() by different groupings

Posted: Fri Mar 05, 2010 2:17 pm
by requinix
Multiple GROUP BYs. What's your query?

Re: count() by different groupings

Posted: Fri Mar 05, 2010 2:51 pm
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.

Re: count() by different groupings

Posted: Fri Mar 05, 2010 3:09 pm
by requinix
Do you find yourself looking at penthouses separately from normal apartments often? Do you need the number of rooms for a penthouse?

Re: count() by different groupings

Posted: Fri Mar 05, 2010 3:21 pm
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.

Re: count() by different groupings

Posted: Fri Mar 05, 2010 3:50 pm
by requinix
There could be, but I'm thinking that separate queries make more sense than trying to do it all at once.

Re: count() by different groupings

Posted: Fri Mar 05, 2010 4:07 pm
by VladSun
tasairis++

I think, even using a UNION should be considered a "separate" query ;)