PDA

View Full Version : Query not returning any data



Atrhick
09-21-2011, 03: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, 06: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, 07: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, 08: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, 08: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, 08: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.