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

I see that you posted some details but can you post data from each table and then what you expect as the result from the query? — Taryn
– Taryn, Commented Feb 15, 2013 at 15:01

Taryn · Accepted Answer · 2013-02-15 17:18:19Z

32

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

answered Feb 15, 2013 at 17:18

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jeepstone Over a year ago

Thank you bluefeet. What does the src bit do at the end?

Taryn Over a year ago

@Jeepstone src is the alias for the subquery.

Jeepstone Over a year ago

I thought so, but interesting that the query doesn't run without it?

Taryn Over a year ago

@Jeepstone some databases require an alias on subqueries and derived tables, MySQL is one that does.

codingbbq Over a year ago

If don't give an alias it throws an error "#1248 - Every derived table must have its own alias". Thanks for the explanation.

|

fancyPants · Accepted Answer · 2013-02-14 17:13:44Z

15

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.

edited Feb 14, 2013 at 17:13

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

answered Feb 14, 2013 at 17:07

Dave S.

6,42934 silver badges36 bronze badges

2 Comments

Jeepstone Over a year ago

Doesn't seem to change anything. I've added sample data.

Dave S. Over a year ago

I wonder if you might try switching jss_orders_headers.* to be jss_orders_headers.orderID. Does that help?

Collectives™ on Stack Overflow

MySQL SUM of a COUNT with GROUP BY clause

jss_order_headers

jss_order_extrafields

COUNT() | orderID | etc

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

jss_order_headers

jss_order_extrafields

COUNT() | orderID | etc

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related