MySQL arithmetic average query

Question

I have a table which describes a restaurant warehouse. In this tabel I have field ID autoincrement, WID, quantity, unit_price, dimension, name. IDs are unique but let me show a php script which describes what I'm doing while inserting

if(isset($_POST['refill_date'])){
    $id = intval($_POST['item_id']);
    $wid = intval($_POST['item_wid']);
    $name = $_POST['name'];
    $qty = floatval($_POST['quantity']);
    $dimen = $_POST['dimension'];
    $tp = floatval($_POST['total_price']);
    $up = floatval($_POST['unit_price']);
    $dist_id = intval($_POST['dist_id']);
    $waybill = $_POST['waybill'];
    $refill_date = $_POST['refill_date'];
    if(!empty($wid) && !empty($id)){
        $insert = $MySQL->query("INSERT INTO warehouse (wid,name,quantity,dimension,unit_price,refill_date,distributor_id,waybill) VALUES ($wid,'$name',$qty,'$dimen',$up,'$refill_date',$dist_id,'$waybill')");
        $update = true;
    }else{
        $insert = $MySQL->query("INSERT INTO warehouse (name,quantity,dimension,unit_price,refill_date,distributor_id,waybill,date) VALUES ('$name',$qty,'$dimen',$up,'$refill_date',$dist_id,'$waybill',now())");
        $wid = mysql_insert_id();
        $update = $MySQL->query("UPDATE warehouse SET wid = $wid WHERE id = $wid");
    }
    if($insert === true && $update === true)
        echo "success";
    else
        echo "failure";
    exit;
}

So you can see that if product does not exist it creates new one and assigns new ID to WID, and in future when the same product will be added the WID will be the same but ID will be increased. Now my task is to output distinct data, but the quantity should be summed up and the unit price should be calculated arithmetic average. I have 13 records with WID 65 and all these have different unit prices so all unit prices should be summed and divided by 13. All this will output like one record. Is it possible to do?

Have a look at the AVG function. You'll (probably) need a where clause, and a GROUP BY — Hobo
– Hobo, Commented Dec 20, 2015 at 9:14
And please, please learn about SQL injection. A malicious person could pass a carefully crafted name value to your code do unwanted things with your data. Obligatory XKCD reference — Hobo
– Hobo, Commented Dec 20, 2015 at 9:19

geass94 · Accepted Answer · 2015-12-22 13:55:56Z

1

$MySQL->query("SELECT DISTINCT id,wid,name,SUM(`quantity`) `QTY`,dimension,AVG(`unit_price`) `AVG`,refill_date,date,edit_date,distributor_id,waybill FROM warehouse GROUP BY wid LIMIT 20 OFFSET $offset");

This worked for me and gave all the results I wanted.

answered Dec 22, 2015 at 13:55

geass94

872 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL arithmetic average query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related