Multiple sum queries with one query

Question

I would like to make a collection according to some fields in the product information and product history table in the products table how do I do this in a single query?

my sample code:

    <table> 
    <thead> 
        <tr> 
            <th>Product</th>
            <th>Model</th>
            <th>Actıve Total</th>
            <th>Pasıve Total</th>
            <th>Color Total</th>
        </tr> 
    </thead> 
    <tbody> 
<?php 

$product = $db->get_results("SELECT * FROM product WHERE (status='ACTIVE')");


foreach ($product as $p ){

$active_total   = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='AKTIVE'");
$pasive_total   = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='PASIVE'");
$color_total    = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='ACTIVE' AND color='BLUE'");
?>      
        <tr> 
            <td><?php echo $p->name; ?></td>
            <td><?php echo $p->model; ?></td> 
            <td><?php echo $active_total; ?></td> 
            <td><?php echo $pasive_total; ?></td> 
            <td><?php echo $color_total; ?></td>
         </tr> 
<?php } ?>
    </tbody> 
</table>

Thanks.

Hi, I haven't figured it out in a week. I asked questions on a sample database. The codes on the actual database are not working. trying to develop the samples you give me the answer to the problem that helps me correct the correct answer. Thank yoou for help. — Yunus Kocabay, Nov 05 '18 at 17:32
If you can provide some more info it might be possible to help resolve your issues. — Nick, Nov 05 '18 at 21:20

Nick · Accepted Answer · 2018-11-02T05:17:18.380

You can use conditional aggregation to get these results from one query:

$sql = "SELECT
            SUM(CASE WHEN status='AKTIVE' THEN price ELSE 0 END) AS aktive,
            SUM(CASE WHEN status='PASIVE' THEN price ELSE 0 END) AS pasive,
            SUM(CASE WHEN status='AKTIVE' AND color='BLUE' THEN price ELSE 0 END) AS color,
        FROM product_history 
        WHERE pid='$p->pid'");

You could actually merge both your queries into 1 using a LEFT JOIN:

$sql = "SELECT p.*,
               SUM(CASE WHEN h.status='AKTIVE' THEN h.price ELSE 0 END) AS aktive,
               SUM(CASE WHEN h.status='PASIVE' THEN h.price ELSE 0 END) AS pasive,
               SUM(CASE WHEN h.status='AKTIVE' AND h.color='BLUE' THEN h.price ELSE 0) AS color
        FROM product p
        LEFT JOIN product_history h ON h.pid = p.pid
        WHERE p.status='ACTIVE'
        GROUP BY p.pid";

If the only value of interest from product is pid (for the purposes of matching against the pid value in product_history) you should change the p.* in the query to p.pid. Otherwise you should ideally enumerate the specific fields whose values you want. See Why is SELECT * considered harmful?

score 1 · Answer 2 · answered Nov 01 '18 at 23:16

I guess you want to know all colors and not only blue? In that case, I'd go with a LEFT JOIN

SELECT p.pid, p.name, SUM(ph.price) AS total, ph.color, ph.status 
FROM product p
LEFT JOIN product_history ph ON (p.pid = ph.pid)
WHERE p.status = 'ACTIVE'
GROUP BY p.pid, p.name, ph.status, ph.color

FYI: You should really use prepared statements to handle the context change and to avoid any kind of problems and vulnerabilities.

Multiple sum queries with one query

2 Answers2