Why count not working in first query but works in second query

hi all

why count not working in first query but works in second query

what do i have to change in first query to make count work

i tried it as a sub query but it doesnt works and doesnt show count value

<?php
// first query count doesnt works
$qry = "select user_name,
(
select count(user_name) as count from cart_table
) 
from cart_table where user_name is not null group by user_name";
$result = mysql_query($qry);
while($row=mysql_fetch_array($result))
{
    echo $row['user_name']."(".$row['count'].")";
}
?>

second query count works

<?php     

$qry = "select user_name, count(user_name) as count from cart_table where user_name is not null group by user_name";
$result = mysql_query($qry);
while($row=mysql_fetch_array($result))
{
    echo $row['user_name']."(".$row['count'].")";
}
?>    

vineet

Just use the second query?

1 Like

The first query does not have any aggregation while you still use GROUP BY in it and that is wrong. The aggregation which is done in the sub-query should be restricted using WHERE clause

So it could be something like:

select user_name,
(
select count(user_name) as count from cart_table where user_name = t1.user_name
) 
from cart_table t1 where user_name is not null 

hi bmbsa

this query doesnt returns count value.
count values are not showing.

secondly you mean to say that “WHERE” clause is compulsory in sub query ??

vineet

The second query is actually the correct query, and far more efficient than the one you’re trying to force to work. Use what’s correct and appropriate.

As others already noted, the second query is the correct one.

However, if you still want to use the first one for any reason, your subquery should have an alias after ()

so use it like

user_name, ( … ) as count

Yes, where clause is compulsory in sub-queries if you want to aggregate based on a specific value, in your case it’s user_name.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.