I need help with the following query. One table with price <= average price in same table by type of product defined by 'Type' results into table3
I need to output the results of a comparison between the value of a budget amount and average value of prices in the products table, of a certain type. The products selected are lower or equal in price to the average amount. The products in the table have a column that sorts them by type. the comparison will only include products in that type. So far I have this:
<?php
$presupuesto = 250;
$catprom = 0;
function mostrarDatos ($resultado) {
if ($resultado !=NULL) {
echo "- Promedio de Categoria ".'Type'." es:".$resultado['total_average']."<br/>";}
else {echo "<br/>No hay más datos!!! <br/>";}
}
$link = mysqli_connect("db", "user", "pass");
mysqli_select_db($link, "db");
$result1 = mysqli_query($link, "SELECT AVG( Price ) as total_average FROM`Table2` GROUP BY Type");
$result2 = mysqli_query($link, "INSERT INTO Table3 (Producto_ID, Name, Description, Price) SELECT Type FROM Table2 WHERE Price <= total_average;");
while ($fila = mysqli_fetch_array($result1)){
mostrarDatos($fila);
}
//while ($fila = mysqli_fetch_array($result2)){
// mostrarDatos3($fila);
//}
mysqli_free_result($result1);
//mysqli_free_result($result2);
mysqli_close($link);
?>
I know I am using old php, but again, havent found resources that present this in PDO
Sample table1:
Budget_ID|Budget
---------|------
01 |300
Table 2:
Product_ID|Name |Description |Price|Type
----------|-----|----------------|-----|----
01 |Milk |1 Ltr of Milk |200 |Drink
02 |Sugar|1 Pound of Sugar|600 |Sweetener
03 |Tea |1 Ltr of Tea |250 |Drink
Table 3:
Product_ID|Name |Description |Price|Type
----------|-----|----------------|-----|----
01 |Milk |1 Ltr of Milk |200 |Drink
You can calculate average first, and select everything below that value.
select p.* from products p
where p.price < (select sum(e.price) / count(e.id) from products e)