//select sum(pos.balance * coalesce(units.formula,1)) as on_order,poi.uniq_key var NotInStatuses = new string[] {"CANCEL","CLOSED"}; NotInStatuses.Dump(); var query1 = from poi in mvw_PoItems join pom in mvw_PoMain on poi.ponum equals pom.ponum join si in mvw_SupplierInfo on pom.uniqsupno equals si.uniqsupno join pos in mvw_PoItemsSchedule on poi.uniqlnno equals pos.uniqlnno join inv in mvw_Inventory on poi.uniq_key equals inv.uniq_key join units in mvw_Units on inv.pur_lunit equals units.to into unitsjoin from myUnits in unitsjoin.DefaultIfEmpty() where !NotInStatuses.Contains(pom.postatus) && pos.balance != 0 && poi.poittype != "MRO" && poi.lcancel != true orderby poi.uniq_key select new { poi.uniq_key, poi.ponum, pos.schd_date, balance = (decimal?)pos.balance * (decimal?)((decimal?)myUnits.formula ?? (decimal?)1), si.supname }; var q2 = (from q1 in query1 group q1 by q1.uniq_key into grouped select new { grouped.Key, balance = grouped.Sum (g => g.balance) }).OrderBy (q => q.Key); q2.Dump(); query1.Dump();