SQL query with different functions on multiple groupings

Question

I am trying to compare the revenue of certain items sold to the warranty costs for those items. The code I have looks something like:

SELECT DISTINCT od.orderID, pa.partID, pa.price AS revenue, wa.cost AS warcost, co.description AS wardesc
FROM orders od
LEFT OUTER JOIN parts pa ON pa.orderID = od.orderID
LEFT OUTER JOIN warranties wa ON pa.partID = wa.partID
LEFT OUTER JOIN malfunctions ml ON ml.malfID = wa.malfID
LEFT OUTER JOIN components co ON ml.compID = co.compID

which returns something like the below.

orderID  | partID | revenue |   warcost |   wardesc<br>
1   |   1001    | 100   |   45  |   Issue #1<br>
1   |   1001 |  100  | 50  | Issue #2<br>
1   |   1002 |  200  | 55    |  Issue #3<br>
2   |   1003 |  300  | 65    |  Issue #3<br>
2   |   1003 |  300  | 70    |  Issue #4

Grouping by order, I would like to SUM the warcost, but only SUM the revenue by individual partID's, so the revenue is not counted twice. I have found this impossible to do!

End result should like the below, look forward to some assistance!

orderID |   partID |        revenue |   warcost |   wardesc<br>
1 |     1001,1002    | 300   |  150  |  Issue #1, Issue #2, Issue #3<br>
2    |  1003     |  300  |  135  |  Issue #3, Issue #4

Answer 1

Try this:

SELECT od.orderID,
  GROUP_CONCAT(pa.partID) AS parts,
  SUM(pa.price) AS revenue,
  SUM(wa.cost) AS warcost,
  GROUP_CONCAT(co.description) AS wardesc
FROM orders od
LEFT OUTER JOIN parts pa ON pa.orderID = od.orderID
LEFT OUTER JOIN warranties wa ON pa.partID = wa.partID
LEFT OUTER JOIN malfunctions ml ON ml.malfID = wa.malfID
LEFT OUTER JOIN components co ON ml.compID = co.compID
GROUP BY od.orderID

sqlfiddle demo ( this is a simplification of what you have, based on the results of your query)

---

EDIT:

Getting duplicate values is probably because you might have multiple matches in one or more of those joins. If you see that you can't remove the duplicates and your original query has the correct results you can do:

SELECT orderID,
  GROUP_CONCAT(partID),
  SUM(revenue),
  SUM(warcost),
  GROUP_CONCAT(wardesc)
FROM (
  SELECT DISTINCT od.orderID AS orderID,
    pa.partID AS partID,
    pa.price AS revenue,
    wa.cost AS warcost,
    co.description AS wardesc
  FROM orders od
  LEFT OUTER JOIN parts pa ON pa.orderID = od.orderID
  LEFT OUTER JOIN warranties wa ON pa.partID = wa.partID
  LEFT OUTER JOIN malfunctions ml ON ml.malfID = wa.malfID
  LEFT OUTER JOIN components co ON ml.compID = co.compID
  ) a
GROUP BY orderID