> I'm trying to output the results like this > Total Orders Failed Orders Total Errors Wrong OverPick Damage > Missing > 1/1/2008 5 4 14 2 3 8 1 > 1/2/2008 5 2 15 6 2 2 5 > > Total Orders: Count of ID's grouped in the Date > Failed Orders: Should check to see if "wrong, overpick, damage, missing are > > 0. that marks it as a failed order. > Total Errors: Sum of wrong, overpick, damage, missing
Brian, Your best bet is to use the SQL functions COUNT() and SUM() to help you aggregate the data by order date. Your text descriptions of the total orders, failed orders, and total errors columns describe almost exactly what the SQL will look like. > Total Orders: Count of ID's grouped in the Date Becomes COUNT(id) AS total_orders > Failed Orders: Should check to see if "wrong, overpick, damage, missing are > > 0. that marks it as a failed order. Becomes SUM( CASE WHEN wrong <> 0 OR overpick <> 0 OR damage <> 0 OR missing <> 0 THEN 1 ELSE 0 END ) AS failed_orders The CASE..WHEN clause is simply setting a value of 1 or 0 for each record where the wrong, overpick, damage, or missing column is not zero. When the final grouping happens, these 1s and 0s get summed to determine the failed_order value. > Total Errors: Sum of wrong, overpick, damage, missing Becomes SUM( wrong + overpick + damage + missing ) AS total_errors Hope this helps, Phillip ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3122 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
