MySQL count all outside limit

I have a MySQL query which counts hours we have worked from the top five of our clients. This works fine, and I am plotting the data on a graph. What I am trying to achieve is a calculation withi开发者_如何学Cn the query that returns a sixth result set which is the sum of all the hours which are not in the top five. i.e. An 'Other' result, so that the sum of all 6 results would equal the total of sum of projects.hours.

My query:

SELECT
  SUM(projects.hours) AS total_hours
FROM projects
GROUP BY projects.companyID
ORDER BY total_hours DESC
LIMIT 5

---

SELECT
SUM(projects.hours) AS total_hours,
(SELECT SUM(projects.hours) FROM projects) AS all_hours
FROM 
projects
GROUP BY 
projects.companyID
ORDER BY total_hours DESC
LIMIT 5

Subtract total_hours from all_hours in your application.

---

SELECT
  1 no,
  projects.company,
  SUM(projects.hours) AS total_hours
FROM 
  projects
GROUP BY 
  projects.companyID
ORDER BY
  total_hours DESC
LIMIT 5

UNION ALL

SELECT
  2 no,
  'Others',
  SUM(projects.hours) AS total_hours
FROM 
  projects
WHERE projects.companyID NOT IN 
( SELECT companyID 
  FROM 
  (  SELECT
      projects.companyID
    FROM 
      projects
    GROUP BY 
      projects.companyID
    ORDER BY
      SUM(total_hours) DESC
    LIMIT 5
  ) AS tmp
)

---

How about a combination of both approaches, with the addition of a rollup we can remove the need for the IN

(I'm not sure if you can rollup with a limit though, I don't have MYSQL here so can't test, sorry)

NOTE: I would expect this to return a company id of NULL that contains the total number of other rows,

SELECT companyID, 
CASE WHEN companyID IS NULL THEN 
    sum(Total_hours)
ELSE
    sum(Total_hours) * -1
END

FROM 
(           
    SELECT companyID,SUM(projects.hours) AS total_hours,  
    FROM  projects 
    GROUP BY  projects.companyID 
    ORDER BY total_hours DESC WITH ROLLUP LIMIT 5 
    UNION ALL
    SELECT null,SUM(projects.hours)*-1 total_hours FROM projects  
)
GROUP BY companyID