CODEWITHSUNDEEP
mysqlsql
skinkelynet
skinkelynet

Reputation: 987

SUM() of COUNT() column in MySQL

I have the following query:

SELECT COUNT(employees.id) AS emp_count 
FROM `orders` 
INNER JOIN `companies` ON `companies`.`id` = `orders`.`company_id` 
INNER JOIN employees ON employees.company_id = companies.id
  AND (employees.deleted_at >= companies.activate_at OR employees.deleted_at IS NULL)                       
  AND employees.created_at <= companies.activate_at 
WHERE 
  (companies.activate_at BETWEEN '2012-01-31 23:00:00' AND '2012-02-29 22:59:59'                    
   AND orders.type = 'Order::TrialActivation'
   AND orders.state = 'completed')

I need the SUM of all the "emp_count" columns. Currently I iterate over all the rows returned by above query, and then SUM on "emp_count" in Ruby. But there are many rows, so this takes up a lot of memory.

How would I SUM on "emp_count" in SQL and return just this number?

Upvotes: 0

Views: 10257

Answers (1)

cegfault
cegfault

Reputation: 6632

--- Update: ---

Since the question has been updated, I'll update my answer:

If you are trying to just get the number of rows from your query based on the WHERE syntax, then you can use COUNT(). However, if you want the sum of all of the employees.id values from the query, then change COUNT(employees.id) to SUM(employees.id), and that should do what you want.

--- Original Answer ---

Try using a subquery, kinda like this (code not tested):

SELECT SUM(x.emp_count) FROM (
    SELECT COUNT(employees.id) AS emp_count 
    FROM `orders` 
    INNER JOIN `companies` ON `companies`.`id` = `orders`.`company_id` 
    INNER JOIN employees ON employees.company_id = companies.id
      AND (employees.deleted_at >= companies.activate_at OR employees.deleted_at IS NULL)                       
      AND employees.created_at <= companies.activate_at 
    WHERE 
      (companies.activate_at BETWEEN '2012-01-31 23:00:00' AND '2012-02-29 22:59:59'                    
       AND orders.type = 'Order::TrialActivation'
       AND orders.state = 'completed')
) x;

You can read more on subqueries in the MySQL documentation, and see also: How to SUM() multiple subquery rows in MySQL?

Upvotes: 2

Related Questions