...

View Full Version : Query not returning any data



Atrhick
09-21-2011, 04:22 PM
Hey guys I an trying to get the average price but i am only getting 0




$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>";

Atrhick
09-21-2011, 07:48 PM
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?

Old Pedant
09-21-2011, 08:39 PM
"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:


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.

Atrhick
09-21-2011, 09:18 PM
"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:


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

Old Pedant
09-21-2011, 09:49 PM
I dunno what happens if some of the data is numeric and some is not.

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



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:


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:


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:


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???

Old Pedant
09-21-2011, 09:57 PM
For example:


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum