MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Andy Shellam Date:September 10 2008 8:14pm
Subject:Re: Weird problem with mysql_query
View as plain text  
I don't have that much experience with MySQL having mostly worked with 
MSSQL, but I'm sure the logic is still the same.  I believe the query 
"select count(*) as 'count' from logins GROUP BY dawiz" will fail 
because "dawiz" is not a column, it's a value within the table.

If I'm not mistaken, a GROUP BY is only required when the COUNT needs to 
be split into groups:

e.g.

select player, count(*) as 'Count' from logins GROUP BY player;

player   | Count
---------------
player1 |  12
player2 |  7
player3 | 35

or:
select count(*) as 'Count' from logins WHERE player = 'player1';

Count
-----
12

or:
select count(*) as 'Count' from logins;

Count
-----
54

Regards,

Andy

Darryle Steplight wrote:
> Hi G,
>    There is nothing weird about your results. When you do a Count(*)
> without a GROUP BY(someColumn) you are essentially asking MySQL how
> many rows are present in the table. But when you do use Group By
> someColum , you are asking MySql how many  rows do I have of
> "someColumn" .  It's just a good practice to use GROUP BY when you
> want to a count of a specific column .
>
> mysql> select count(*) as 'Count' from logins GROUP BY dawiz
>
> The above query should return the results you are looking for.
>
>
>
> On Tue, Sep 9, 2008 at 6:06 PM, MySql <mysql@stripped> wrote:
>   
>> We are running MySql version 5.0.45-Debian_1ubuntu3.1-log Debian etch
> distribution under Ubuntu.
>> If I submit the following query via mysql_query it acts as if the where is not
> there:
>>
>> select count(*) as 'Count' from logins where player = 'aqwert';
>>
>> this returns:
>>     Count
>> 143578160
>>
>>
>> Submitting the same query at a MySql prompt works correcty:
>>
>> mysql> select count(*) as 'Count' from logins where player = 'dawiz';
>> +-------+
>> | Count |
>> +-------+
>> |  6026 |
>> +-------+
>> 1 row in set (0.00 sec)
>>
>> Modifying the query to use a group by returns the correct count:
>>
>>    Total    Count
>>    Total     6026
>>
>> Is there something I should know about mysql_query and a simple count(*)?
>>
>> G Vaughn
>>
>>     
>
>   
Thread
Weird problem with mysql_queryMySql10 Sep
  • Re: Weird problem with mysql_queryDarryle Steplight10 Sep
    • Re: Weird problem with mysql_queryAndy Shellam10 Sep