Multiple aggregate functions in MySQL with different conditions

I am running the following query

SELECT t2.lender_name, COUNT(t1.id) as total,    
SUM(t1.submit_date IS NULL) AS num_incomplete,

(SELECT AVG(DATEDIFF(due_date,now())) 
  FROM table_1 WHERE submit_date IS NULL ) as avg_incomplete_due_in,
(SELECT AVG(DATEDIFF(due_date,submit_date))
  FROM table_1 WHERE submit_date IS NOT NULL) as avg_complete_turnaround

FROM table_1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

The total, num_incomplete and the grouping works great. The sub select values are the same for each row. I would like those values grouped by the lender_name also and returned as part of the same recordset. Any suggesti开发者_运维技巧ons?

Your current code just lacks a relation between the outer query and the subqueries. In theory, you just need to correlate the queries:

SELECT t2.lender_name, COUNT(t1.id) as total,
SUM(t1.submit_date IS NULL) AS num_incomplete,
 (SELECT AVG(DATEDIFF(due_date,now())) 
  FROM table_1 t3
  WHERE submit_date IS NULL
    AND t3.lender_name = t2.lender_name) as avg_incomplete_due_in,
 (SELECT AVG(DATEDIFF(due_date,submit_date))
  FROM table_1
  WHERE submit_date IS NOT NULL
    AND t3.lender_name = t2.lender_name) as avg_complete_turnaround
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

In practice, the query is not very efficient in MySQL. You can rewrite it in the following way:

SELECT
  t2.lender_name,
  COUNT(*) as total,
  SUM(t1.submit_date IS NULL) AS num_incomplete,
  AVG(IF(t1.submit_date IS NULL,
         DATEDIFF(t1.due_date, NOW()),
         NULL)) AS avg_incomplete_due_in,
  AVG(DATEDIFF(due_date,submit_date)) AS avg_complete_turnaround
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

sum if, and count if do the trick

SELECT t2.lender_name, COUNT(t1.id) as total,    
SUM(t1.submit_date IS NULL) AS num_incomplete,
SUM(IF(table_1.submit_date IS NULL,DATEDIFF(table_1.due_date,now()),0)) / COUNT(IF(table_1.submit_date IS NULL,1,NULL)) as avg_incomplete_due_in
SUM(IF(table_1.submit_date IS NOT NULL,DATEDIFF(table_1.due_date,submit_date),0)) / COUNT(IF(table_1.submit_date IS NOT NULL,1,NULL)) as avg_complete_turnaround

FROM table_1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name