Mysql子查询平均为7天和30天

Question

I have a table of responses from a questionnaire. I want to be able to put 2 queries i currently have together into one query. I've been trying to do this for a couple of days, and have searched this site a lot.

Here is what I have so far:

select
responses.gname,
responses.client_id,
responses.pri_cou_last,
responses.olm_team,
responses.work,
responses.resDate,
(SELECT round (avg(responses.response),2) FROM responses WHERE responses.formID='2'     AND responses.resDate<=curdate() AND responses.resDate>=DATE_SUB(NOW(), INTERVAL 30 DAY) AND responses.response>'0')
 AS avg30,
    (SELECT round(avg(responses.response),2) FROM responses WHERE responses.formID='2' AND responses.resDate<=curdate() AND responses.resDate>=DATE_SUB(NOW(), INTERVAL 7 DAY) AND    responses.response>'0')
 AS avg7
from responses
    GROUP BY responses.gname,responses.client_id
    ORDER BY responses.pri_cou_last;

The two sub queries are not doing what i want, most likely because they are wrong. They give me a total average for all responses over the 7 and 30 day intervals.

Here is the code for a 7 day query that works for me:

SELECT
responses.gname,
responses.olm_team,
responses.work,
round(avgresponses.response),2),
responses.pri_cou_last,
responses.client_id,
visits.client_id,
visits.bed,
visits.depdate_ymd
FROM
responses
LEFT JOIN
visits ON responses.client_id=visits.client_id
WHERE
responses.formID='2'
AND responses.resDate<=curdate() 
 AND responses.resDate>=DATE_SUB(NOW(), INTERVAL 7 DAY) 
 AND responses.response>'0' 
 AND visits.bed>'0' 
 AND visits.depdate_ymd='0000-00-00'
 GROUP BY
 responses.gname,
 responses.client_id
 ORDER BY
 responses.pri_cou_last

Thank you in advance!

Thanks for the responses so far.

Yes, there is a formID column, and it is spelled the same.

Here is the code I have now:

 SELECT
   r1.gname,
   r1.client_id,
   r1.pri_cou_last,
   r1.olm_team,
   r1.work,
   r1.resDate,
   round(AVG(CASE WHEN formID = '2' AND response > '0' AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND CURDATE() THEN response end),2) as a7,
   round(AVG(CASE WHEN formID = '2' AND response > '0' AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND CURDATE() THEN response end),2) as a3,  
   visits.client_id,
   visits.bed,
   visits.depdate_ymd
    FROM
      responses as r1
 LEFT JOIN visits on r1.client_id=visits.client_id
    WHERE visits.bed>'0' 
     GROUP BY r1.client_id,r1.pri_cou_last
     ORDER BY r1.pri_cou_last;

The problem is the data isn't what i want. There are NULL values returned for a3 and a7 on some lines. And, there are not the right number of rows.

After reviewing the data returned by the above query with the client we found that the data from this query is correct. It's providing more data than the original query, and they way the client enters data into the program causes some unexpected responses from this query. But they are correct...

I checked the 1 answer below, because using CASE did solve the problem. I never could get the correlated queries to work.

Thanks again.

Answer 1

You need to use correlated subqueries:

SELECT
    r1.gname,
    r1.client_id,
    r1.pri_cou_last,
    r1.olm_team,
    r1.work,
    r1.resDate,
    (SELECT round (avg(r2.response),2) 
        FROM responses AS r2
        WHERE r2.formID='2'     
            AND r2.resDate<=curdate() 
            AND r2.resDate>=DATE_SUB(NOW(), INTERVAL 30 DAY) 
            AND r2.response>'0'
            AND r2.gname = r1.gname AND r2.client_id = r1.client_id)
     AS avg30,
     (SELECT round(avg(r3.response),2)
        FROM responses AS r3
        WHERE r3.formID='2'
            AND r3.resDate<=curdate()
            AND r3.resDate>=DATE_SUB(NOW(), INTERVAL 7 DAY)
            AND r3.response>'0'
            AND r3.gname = r1.gname AND r3.client_id = r1.client_id)
     AS avg7
FROM responses AS r1
GROUP BY r1.gname,r1.client_id
ORDER BY r1.pri_cou_last;

You can also do it without subqueries:

SELECT
    r1.gname,
    r1.client_id,
    r1.pri_cou_last,
    r1.olm_team,
    r1.work,
    r1.resDate,
    ROUND(AVG(CASE WHEN formID = '2' 
                    AND response > 0 
                    AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND CURDATE()
                   THEN response
              END) 2) AS avg30,
    ROUND(AVG(CASE WHEN formID = '2' 
                    AND response > 0 
                    AND resDate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND CURDATE()
                   THEN response
              END) 2) AS avg7
FROM responses AS r1
GROUP BY r1.gname,r1.client_id
ORDER BY r1.pri_cou_last;