MySQL SUM of a COUNT with GROUP BY clause

Question

I wish to SUM the COUNT of a query as follows. This query returns the count properly (1) for each row, but not sure how to add them all up.

SELECT COUNT(*), jss_orders_headers.*  FROM jss_orders_headers
LEFT JOIN jss_orders_extrafields
ON jss_orders_headers.orderID = jss_orders_extrafields.orderID
AND jss_orders_extrafields.extraFieldID = 5
GROUP BY jss_orders_headers.orderID
ORDER BY jss_orders_headers.orderID DESC

Table Structure is

jss_order_headers

orderID, etc

jss_order_extrafields

exid, orderID, extrafieldID, extrafieldName, content

This currently returns data as follows:

COUNT() | orderID | etc

1 | 99

1 | 104

1 | 106

I need to return the SUM of the COUNT() column. So in the 3 examples above I would return 3.

Many thanks

Answer 1

Your question is not exactly clear but if you only want the sum() of all orders, then you should be able to use something like this:

select sum(TotalByOrder) TotalOrders
from
(
  SELECT COUNT(*) TotalByOrder, jss_orders_headers.*  
  FROM jss_orders_headers
  LEFT JOIN jss_orders_extrafields
    ON jss_orders_headers.orderID = jss_orders_extrafields.orderID
    AND jss_orders_extrafields.extraFieldID = 5
  GROUP BY jss_orders_headers.orderID
) src

Answer 2

Would WITH ROLLUP do what you need?

SELECT COUNT(*), jss_orders_headers.*  FROM jss_orders_headers
LEFT JOIN jss_orders_extrafields
ON jss_orders_headers.orderID = jss_orders_extrafields.orderID
AND jss_orders_extrafields.extraFieldID = 5
GROUP BY jss_orders_headers.orderID DESC WITH ROLLUP

Why there's no ORDER BY?

When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive. However, you still have some control over sort order. GROUP BY in MySQL sorts results, and you can use explicit ASC and DESC keywords with columns named in the GROUP BY list to specify sort order for individual columns.