My SQL with MAX Function and then grouping & summing values and text

Question

SELECT RegWOSEGSmry.ardate,
  RegWOSEGSmry.arinvno,
  RegWOSEGSmry.artotal,
  RegWOSEGSmry.aryear,
  RegWOSEGSmry.CreationDate,
  RegWOSEGSmry.custpcl,
  RegWOSEGSmry.custsnum,
  RegWOSEGSmry.CxOpenDate,
  RegWOSEGSmry.DistributeFlatRates,
  RegWOSEGSmry.FlatRateAll,
  RegWOSEGSmry.FlatRateAllAmt,
  RegWOSEGSmry.FlatRateLabor,
  RegWOSEGSmry.FlatRateLaborAmt,
  RegWOSEGSmry.FlatRateMisc,
  RegWOSEGSmry.FlatRateMiscAmt,
  RegWOSEGSmry.FlatRateParts,
  RegWOSEGSmry.FlatRatePartsAmt,
  RegWOSEGSmry.HardwareCost,
  RegWOSEGSmry.HardwareStreetPrice,
  RegWOSEGSmry.HardwareTMRPrice,
  RegWOSEGSmry.kbranch,
  RegWOSEGSmry.kcustnum,
  RegWOSEGSmry.kequipnum,
  RegWOSEGSmry.kmfg,
  RegWOSEGSmry.kmodel,
  RegWOSEGSmry.kserialnum,
  RegWOSEGSmry.kswoseg,
  RegWOSEGSmry.kworkorder,
  RegWOSEGSmry.LaborCost,
  RegWOSEGSmry.LaborHours,
  RegWOSEGSmry.LaborStreetPrice,
  RegWOSEGSmry.LaborTMRPrice,
  RegWOSEGSmry.MiscCost,
  RegWOSEGSmry.MiscStreetPrice,
  RegWOSEGSmry.MiscTMRPrice,
  RegWOSEGSmry.oeitemlong,
  RegWOSEGSmry.oeshipname,
  RegWOSEGSmry.oetypeord,
  RegWOSEGSmry.PartsCost,
  RegWOSEGSmry.PartsStreetPrice,
  RegWOSEGSmry.PartsTMRPrice,
  RegWOSEGSmry.RegWOSEGId,
  RegWOSEGSmry.SegCompletedDate,
  RegWOSEGSmry.SegOpenDate
FROM WieseData.dbo.RegWOSEGSmry RegWOSEGSmry
WHERE (RegWOSEGSmry.kcustnum='165453')

Will produce this:

Each RegWOSEGSmry.kworkorder can have multiple RegWOSEGSmry.oeitemlong values, but what I'm interested in is all the MAX oeitemlong values for a particular kworkorder. After that I need to combine all columns into one row to produce the following:

The extra tricky part is that for rows with "999" in the "kswoseg" column, I need to sum all the particular other values, but there are times where the "999" rows have no text/data in the "kequipmnum", "kmfg", "kmodel" and the "kserialnum" fields, but I still need this data to be promoted to the remaining row as in the final picture. I don't care what value remains in the "kswoseg" field.

I know, it's a ton that I'm throwing out here, but this is driving me crazy...

Answer 1

select MAX(RegWOSEGSmry.oeitemlong),RegWOSEGSmry.kserialnum,RegWOSEGSmry.FlatRateLaborAmt,RegWOSEGSmry.kmodel,.....(add all the columns that you want) FROM WieseData.dbo.RegWOSEGSmry RegWOSEGSmry
WHERE (RegWOSEGSmry.kcustnum='165453') group by all the above columns except oeitemlong.

For the null values try the above see if that works.