Multiple Query In SQL

Question

I have a table by name material transaction and I want to display data like Table B

Item Name	Transaction Type	Weight
A	Issue Material	60
B	Issue Material	80
A	Return Material	20
B	Return Material	10
A	Issue Material	20
B	Issue Material	10

I want result like below table Table B

Item Name	Issue Material	Return Material
A	80	20
B	90	10

$query = "SELECT item_name, SUM(material_wt) AS issue_material WHERE transaction_type = 'Issue Material' FROM material_transaction GROUP BY item_name UNION ALL SELECT item_name, SUM(material_wt) AS return_material WHERE transaction_type = 'Reject Material' FROM material_transaction GROUP BY item_name";
                        $select_query_table = mysqli_query($connection,$query);
                        while($run_select_query = mysqli_fetch_assoc($select_query_table)){
                 
                     $item_name = $run_select_query['item_name'];  
                     $wt = $run_select_query['material_wt'];  
                        echo "<td>$item_name</td>";                          
                       echo "<td>$wt</td>";                          
                        echo "</tr>";
                   
                    }

Tim Biegeleisen · Accepted Answer · 2022-11-26 10:59:35Z

2

You may use conditional aggregation:

SELECT
    item_name,
    SUM(CASE WHEN transaction_type = 'Issue Material'
             THEN Weight ELSE 0 END) AS Issue_Material,
    SUM(CASE WHEN transaction_type = 'Return Material'
             THEN Weight ELSE 0 END) AS Return_Material
FROM material_transaction
GROUP BY item_name;

answered Nov 26, 2022 at 10:59

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Multiple Query In SQL

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