List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:January 23 2008 7:31pm
Subject:Re: Newbie: A single number
View as plain text  
Hi Mário, all !


Mário Gamito wrote (re-ordered):
> Sebastian Mendel wrote:
>> Mário Gamito schrieb:
>>> Hi,
>>>
>>> I'm trying to get a single number out of a SELECT statement:
>>>
>>> SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE 
>>> comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by 
>>> comment_approved
>>>
>>> But instead i get two rows:
>>>
>>> |------------------------------------------
>>> |comment_approved | COUNT (comment_agent) |
>>> |------------------------------------------
>>> |0                |                     1 |
>>> |------------------------------------------
>>> |1                |                    47 |
>>> |-----------------------------------------|

"Instead" - maybe contrary to what you desire to get.
But it is exactly what your command asks for:
- Two columns (the expressions following "SELECT")
- for all different values of "comment_approved".

>>>
>>> I've Google about it but found no answer (my bad, probably).

The MySQL manual should be the better place for this, read about the 
SELECT statement.
The exact URL to use depends on the version of MySQL you are running.

>>>
>>> [[...]]
>>
>> just include only the things you want in the SELECT
>>
>> also it seems you require only comment_agent LIKE '%Linux%' and NOT OR 
>> comment_approved=0 if you only need the '47'
>>
>> SELECT COUNT(comment_agent)
>> from wp_comments
>> WHERE comment_agent LIKE '%Linux%'
>> GROUP by comment_approved
>>
> 
>  > Hi Sebastian,
> 
> Thank you for your answer.
> 
> I tried your way, but still, I get a column with two values:
> 
> |---------------------
> |COUNT(comment_agent)|
> |--------------------|
> |                   1|
> ----------------------
> |                  47|
> ----------------------
> 
> What I need is just the 47.
> 
> Any ideas ?

Plenty.

Obviously you currently have a row in your data which satisfies
    comment_agent LIKE '%Linux%'
and has "comment_approved"=0 .

What makes the 47 so special ?
It is the count which is associated with "comment_approved"=1 - right ?
Then simply specify that:

    SELECT COUNT(comment_agent)
    from wp_comments
    WHERE comment_agent LIKE '%Linux%'
          AND comment_approved = 1

As now you specify a single value for "comment_approved", there is no 
need any more to use grouping.


HTH,
Jörg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Thread
Newbie: A single numberMário Gamito21 Jan
  • Re: Newbie: A single numberSebastian Mendel21 Jan
    • Re: Newbie: A single numberMário Gamito21 Jan
      • Re: Newbie: A single numberJoerg Bruehe23 Jan
  • Re: Newbie: A single numberMichael Cole21 Jan
    • Re: Newbie: A single numberMário Gamito21 Jan
  • Re: Newbie: A single numberAndy Wallace21 Jan