RE: FN-FORUM: SQL / programming Q

date posted 3rd October 2006 19:16

Paul Cooper wrote:
> Hi
>
> I'm a bit lost on how to approach a page. I want to display a list
> of surveys that will be taken and alongside each survey I want to
> show how many of the (invited) users have completed the survey. So
> it would be a list like:
>
> Group / Survey Name (4 completed, 38 pending)
>
> Etc.
>
> I store the surveys in tblGroups and the list of invited users in
> tblResults. A field in the latter called SubmittedDate stores the
> date a user completed the survey, so if this is blank I know they
> still haven't taken the survey.

When you say blank, do you mean NULL?

> This is the data structure:
>
> tblGroups
> GroupID
> GroupName
>
> tblResults
> GroupID (FK to above)
> ClientID (stores the invited user)
> SubmittedDate

If it is NULL and assuming a sensible database then something like

SELECT g.GroupID,g.GroupName,count(r.ClientID) AS
NumTotal,count(r.SubmittedDate) AS NumComplete FROM tblGroups as g INNER
JOIN tblResults as r ON g.GroupID = r.GroupID GROUP BY g.GroupID,g.GroupName
ORDER BY g.GroupName

Should work, then just subtract complete from total to get pending. This
should work because count(field) does not include records where the field is
NULL.

If the field is not NULL then I would suggest changing so it is, otherwise I
believe it will need two queries, or a single query and then a loop in your
code to calculate the completed and pending.

HTH,

Dai