MYSQL select where SUM()

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               

Leave a Reply

Your email address will not be published. Required fields are marked *