MYSQL select where SUM() Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of MYSQL select where SUM() without wasting too much if your time.

The question is published on by Tutorial Guruji team.

What I’m trying to do is see if there is material associated with a work order. So I’m joining the work order material table, grouping it by “Work Order” then using sum() to find the total amount of quantity associated with the work order. The only thing that matters about the amount is if it is more than 0 or not. if it is more than 0 I want it to come up in my result, if not I don’t want it to be in my result.

                SELECT `work_order`.`PO`
                        ,`work_order`.`Work Order`
                        ,`work_order`.`Part`
                        ,`work_order`.`Revision`
                        ,`work_order`.`Description`
                        ,`work_order`.`Drawing`
                        ,`work_order`.`Quantity`
                        ,`work_order`.`CSM`
                FROM `work_order`
                LEFT JOIN `wo_material`
                    ON `work_order`.`Work Order`=`wo_material`.`WO`
                WHERE `work_order`.`CSM` OR IFNULL(SUM(IFNULL(`wo_material`.`Quantity`,0)),0)
                GROUP BY `work_order`.`Work Order`

The error is #1111 – Invalid use of group function. If i change the WHERE line to

WHERE `work_order`.`CSM`

The query has no error but my result is incorrect.

On work_order Work Order is primary.

On wo_material WO is not primary or unique, It has another column RN that is primary.

This is just a query not a procedure.

MySQL Version 5.6.2

Answer

use having clause to remove rows that have zero quantity

           SELECT `work_order`.`PO`
                    ,`work_order`.`Work Order`
                    ,`work_order`.`Part`
                    ,`work_order`.`Revision`
                    ,`work_order`.`Description`
                    ,`work_order`.`Drawing`
                    ,`work_order`.`Quantity`
                    ,`work_order`.`CSM`
            FROM `work_order`
            LEFT JOIN `wo_material`
                ON `work_order`.`Work Order`=`wo_material`.`WO`
            GROUP BY `work_order`.`Work Order`
            HAVING  SUM(IFNULL(`wo_material`.`Quantity`,0)) > 0               
We are here to answer your question about MYSQL select where SUM() - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji