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'].")";
}
?>
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
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.