0

Following on from this question I posed earlier, say I want to add ID to the query:

SELECT client, ID, job_type, SUM(actual_value_fee) FROM jo2details GROUP BY client, job_type WITH ROLLUP

but I don't want MySQL to try to total the ID column as it's an identifier for the records on the table rather than something "summable". How can I exclude ID from the WITH ROLLUP please?

Community
  • 1
  • 1
user114671
  • 532
  • 1
  • 9
  • 27

1 Answers1

0

One way is to UNION two queries. The first query will have the id and the second query will generate the subtotals. Using your example, it would look like this:

SELECT a.id, a.client, a.job_type, sum(a.actual_value_fee)
FROM jo2details a
GROUP BY a.client, a.job_type, a.id

UNION

SELECT NULL, b.client, NULL, sum(b.actual_value_fee)
FROM jo2details b
GROUP BY b.client
WITH ROLLUP

From here, you'll have to play around with it to get the subtotals to sort correctly.

Kevin Marsden
  • 677
  • 1
  • 10
  • 17