nested sql count

[homerUK]

Hi guys,

I have two tables

survey
surveyID, title, memberID

responses
responseID, surveyID, date, status

I need to create a query where I can look at each survey and count the number of responses for each. The trouble is, each response has a status of "C" (complete), "P" (paused) and NULL (un finished)

How would I get the following output:

surveyID, title, memberID, num_paused, num_completed, num_incompleted

I have the sql as follows:


SELECT
survey.surveyID as "Survey ID",
survey.title as "Title",
survey.memberID as "Member ID",
(SELECT count (r.responseID) as "Num Paused" FROM responses r WHERE r.surveyID = survey.surveyID AND r.status = "P" GROUP BY r.surveyID)
FROM survey, responses
WHERE survey.surveyID = responses.surveyID
GROUP BY survey.surveyID


It says there is an error near 'SELECT count...'
any ideas where I am going wrong??

thanks :)

[NancyJ]

Off the top of my head I tihnk you need something like


SELECT
survey.surveyID AS "Survey ID",
survey.title AS "Title",
survey.memberID AS "Member ID",
COUNT(responses.surveyID)
FROM
survey
JOIN
responses
ON
survey.surveyID = responses.surveyID
GROUP BY
survey.surveyID

[guelphdad]

This is what you need:


SELECT
survey.surveyID AS "Survey ID",
survey.title AS "Title",
survey.memberID AS "Member ID",
sum(case when status='P' then 1 else 0 end) as Paused,
sum(case when status='C' then 1 else 0 end) as Completed,
sum(case when status is NULL then 1 else 0 end) as Incomplete,
FROM
survey inner join responses
ON survey.surveyID = responses.surveyID
group by
survey.surveyID,
survey.title,
survey.memberID


Note that the group by clause MUST contain all non-aggregate columns from your select clause. In all other databases failing to do so will cause your query to fail. Mysql allows you to omit columns but warns about unpredictable results. See GROUP BY HIDDEN FIELDS in the mysql manual for more information. For the most part I'd say always get in the habit of naming the correct columns.

[homerUK]

This is what you need

thanks guys - that last one work perfectly.
How do I add another table in there? I need to look up the members' email address as well. would i put another join, based on memberID = the survey.memberID ?

also, each response is date stamped (format: DD-MM-YYYY) - how would I get only responses for 7 days previous? Is there an SQL date function to workout where date < 7 days ago?

sorry for all the questions!!

[guelphdad]

yes that is how you would join the next table.

if you have dates in the format you suggest, then you are not using a date type but using a varchar or char type for your dates. You therefore lose all data/time calculations.

Take a look at the link in my signature, you may be able to leave as is and use str_to_date to do your calculations, but you are better off in many ways in correcting your dates to proper yyyymmdd format in a date type column and then using DATE_FORMAT to display your dates in the way you have them stored now.