Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query not returning any data

    Hey guys I an trying to get the average price but i am only getting 0

    PHP Code:

    $query10 
    "SELECT partner_id_buyer, AVG(price_CIQFY_paid) FROM lead_partners_pages GROUP by partner_id_buyer "

    $result10 mysql_query($query10) or die(mysql_error());
    $row10 mysql_fetch_array($result10);

    echo 
    "<div class=\"Avg\">AVG: $" $row10['AVG(price_CIQFY_paid)'] .
    "</div>"

  • #2
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    is there no expert out there can can give me an idea of what i did wrong? the row I an pulling from has data in it, but I was also wondering if it matters that the row is a varchar(10) ... should i change it? is that why its not working?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    "the row I am pulling data from ..."

    ??? If there is only ONE row, then why do you use AVG?

    *******

    But, yes, I wouldn't expect AVG to work on non-numeric data. I just tested, and indeed no matter what, the answer is always zero.

    Why would you have a field named price_CIQFY_paid that is VARCHAR?

    Oh, well...so convert it:
    Code:
    SELECT partner_id_buyer, AVG( CONVERT(price_CIQFY_paid, DECIMAL) )  AS averagePaid
    FROM lead_partners_pages 
    GROUP by partner_id_buyer
    But if you have some non-numeric values in that field, you'll have to work around them, as well.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    "the row I am pulling data from ..."

    ??? If there is only ONE row, then why do you use AVG?

    *******

    But, yes, I wouldn't expect AVG to work on non-numeric data. I just tested, and indeed no matter what, the answer is always zero.

    Why would you have a field named price_CIQFY_paid that is VARCHAR?

    Oh, well...so convert it:
    Code:
    SELECT partner_id_buyer, AVG( CONVERT(price_CIQFY_paid, DECIMAL) )  AS averagePaid
    FROM lead_partners_pages 
    GROUP by partner_id_buyer
    But if you have some non-numeric values in that field, you'll have to work around them, as well.

    Man your really good with Query's!! but hey i have one more problem its showing $0.0000

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    I dunno what happens if some of the data is numeric and some is not.

    Oh, w.t.h. Let me try it.

    Code:
    mysql> describe demo;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | buyer | int(11)     | YES  |     | NULL    |       |
    | paid  | varchar(50) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.06 sec)
    
    mysql> select * from demo;
    +-------+---------+
    | buyer | paid    |
    +-------+---------+
    |   111 | 3.95    |
    |   222 | zamboni |
    |   111 | 2.20    |
    |   222 | 1.98    |
    +-------+---------+
    4 rows in set (0.00 sec)
    Okay, so you can see that the field is VARCHAR and that *one* of the values for buyer=222 is text, not a number.

    So let's try it:
    Code:
    mysql> select buyer, AVG(CONVERT(paid,DECIMAL)) from demo group by buyer;
    +-------+----------------------------+
    | buyer | AVG(CONVERT(paid,DECIMAL)) |
    +-------+----------------------------+
    |   111 |                     3.0000 |
    |   222 |                     1.0000 |
    +-------+----------------------------+
    2 rows in set, 2 warnings (0.06 sec)
    Hmmm...interesting, but obviously the numbers are being rounded to ZERO decimal places.

    Okay, change the CONVERT slightly:
    Code:
    mysql> select buyer, AVG(CONVERT(paid,DECIMAL(20,4))) from demo group by buyer;
    +-------+----------------------------------+
    | buyer | AVG(CONVERT(paid,DECIMAL(20,4))) |
    +-------+----------------------------------+
    |   111 |                       3.07500000 |
    |   222 |                       0.99000000 |
    +-------+----------------------------------+
    2 rows in set, 2 warnings (0.00 sec)
    And that looks right to me.

    Notice that the AVG( ) of "1.98" and "zamboni" is 0.99.

    And that makes sense: Clearly what happens is that CONVERT('zamboni',DECIMAL) is ZERO!

    Let's check it out:
    Code:
    mysql> select buyer, paid, CONVERT(paid,DECIMAL(20,4)) from demo;
    +-------+---------+-----------------------------+
    | buyer | paid    | CONVERT(paid,DECIMAL(20,4)) |
    +-------+---------+-----------------------------+
    |   111 | 3.95    |                      3.9500 |
    |   222 | zamboni |                      0.0000 |
    |   111 | 2.20    |                      2.2000 |
    |   222 | 1.98    |                      1.9800 |
    +-------+---------+-----------------------------+
    4 rows in set, 2 warnings (0.00 sec)
    See? The string that can't be converted to a number is forced to be zero.

    So...

    How much further do you need to take this? Do we need to exclude strings that aren't numbers???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    For example:
    Code:
    mysql> select buyer, CONVERT(paid,DECIMAL(20,4)) from demo
        -> where paid regexp '[0-9]+(\\.[0-9]*)?';
    +-------+-----------------------------+
    | buyer | CONVERT(paid,DECIMAL(20,4)) |
    +-------+-----------------------------+
    |   111 |                      3.9500 |
    |   111 |                      2.2000 |
    |   222 |                      1.9800 |
    +-------+-----------------------------+
    3 rows in set (0.00 sec)
    
    mysql> select buyer, AVG(CONVERT(paid,DECIMAL(20,4))) from demo
        -> where paid regexp '[0-9]+(\\.[0-9]*)?'
        -> group by buyer;
    +-------+----------------------------------+
    | buyer | AVG(CONVERT(paid,DECIMAL(20,4))) |
    +-------+----------------------------------+
    |   111 |                       3.07500000 |
    |   222 |                       1.98000000 |
    +-------+----------------------------------+
    2 rows in set (0.00 sec)2 rows in set (0.00 sec)
    You see it? I'm only accepting values for paid that *look* like numbers. (One or more digits optionally followed by a decimal point and then zero or more digits.)

    So now, when I do AVG() the values, I only get the AVG() of numeric values, and indeed the AVG() for buyer 222 is 1.98, because there is only one numeric value for buyer 222. Specifically, 1.98.
    Last edited by Old Pedant; 09-21-2011 at 09:01 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •