mysql将三个查询合并为一个

Question

I am calculating purchase, return, sales seperately from a single table for this i have query as follows.

$stmt = $pdo->prepare("SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='purchase') AND sale_date BETWEEN '$from_date' and '$to_date'"); $stmt->execute();
$products_in = $stmt->fetchAll();
foreach($products_in as $product_in){
                        $purchase = $product_in['quantity_in'];
                    }

                    $stmt = $pdo->prepare("SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='return purchase') AND sale_date BETWEEN '$from_date' and '$to_date'"); $stmt->execute();
                    $products_in = $stmt->fetchAll();
                    foreach($products_in as $product_in){
                        $return_purchase = $product_in['quantity_in'];
                    }

                    $stmt = $pdo->prepare("SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='initial_stock') AND sale_date BETWEEN '$from_date' and '$to_date'"); $stmt->execute();
                    $products_in = $stmt->fetchAll();
                    foreach($products_in as $product_in){
                        $initial_stock = $product_in['quantity_in'];
                    }

The above takes a very long time, if there were 2000 products it takes neraly 5 minutes to calculate, is there any way to combine the above three queries into one, so that it can run FASTER. maybe like this,

(SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='initial_stock'))
as opening, 
(SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='purchase')) as purchase
WHERE
sale_date BETWEEN '$from_date' and '$to_date'

Note: im not good at MySQL, I've only run simple queries through PHP until now.

Answer 1

I would use an group by statement where you can query everything in one query.

SELECT type, sum(quantity) as quantity_in, sum(total) as total_in 
FROM silk 
WHERE full_name = '$full_name' AND sale_date BETWEEN '$from_date' and '$to_date'
group by type

And in that case it would give you this in your PHP Code

$stmt = $pdo->prepare("SELECT type, sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND sale_date BETWEEN '$from_date' and '$to_date' group by type"); $stmt->execute();
$products_in = $stmt->fetchAll();
foreach($products_in as $product_in){
    switch ($product_in['type']) {
        case 'purchase':
            $purchase = $product_in['quantity_in'];
        break;
        case 'return purchase':
            $return_purchase = $product_in['quantity_in']
        break;
        case 'initial_stock':
            $initial_stock = $product_in['quantity_in'];
        break;
        // and so on...
    }
}