mysql join three queries into one

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.

Mathieu Rodic · Accepted Answer · 2016-01-18 11:05:20Z

1

Why not using a GROUP BY clause?

SELECT type, SUM(quantity) AS quantity_in, SUM(total) AS total_in
FROM silk
WHERE full_name = '$full_name'
AND type IN ('purchase', 'return purchase', 'initial_stock')
AND sale_date BETWEEN '$from_date' and '$to_date'
GROUP BY type

edited Jan 18, 2016 at 11:05

answered Dec 1, 2014 at 9:05

Mathieu Rodic

6,7702 gold badges45 silver badges49 bronze badges

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

Comments

Arto Uusikangas · Accepted Answer · 2014-12-01 09:23:14Z

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...
    }
}

Tali Luvhengo · Accepted Answer · 2014-12-01 09:07:39Z

0

Try

$stmt = $pdo->prepare("SELECT type, 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' GROUP BY type"); 
$stmt->execute();
$products_in = $stmt->fetchAll();

edited Dec 1, 2014 at 9:07

answered Dec 1, 2014 at 9:06

Tali Luvhengo

3591 silver badge15 bronze badges

5 Comments

Mathieu Rodic Over a year ago

Wouldn't you miss the other types (aka. 'purchase' and 'initial_stock')?

Tali Luvhengo Over a year ago

@MathieuRodic No this would pull the types in the database.

Arto Uusikangas Over a year ago

No because you have a where statement that states that type needs to equal return purchase.

Mathieu Rodic Over a year ago

Yes. But V Arun Kumar wanted to return the results for 'purchase', 'return purchase' and 'initial_stock' in one query.

Arto Uusikangas Over a year ago

Yes, and therefor the query that Talifhani wrote will not do the trick because of the WHERE statement that limits the query to only return purchases. so @Mathieu we are on the same page.

Collectives™ on Stack Overflow

mysql join three queries into one

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related