Hey Gang,
I have been trying to figure out this query. This is what I have that works…
SELECT DISTINCT P.FIRSTNAME, P.LASTNAME, P.GEOGRAPHY,
FROM USAGE U, PERSON P
WHERE U.IDPERSON = P.IDPERSON AND U.DISPLAY = 1 AND U.SUBMISSIONDATE > 'startdate' AND U.SUBMISSIONDATE < 'enddate'
GROUP BY P.GEOGRAPHY, P.FIRSTNAME, P.LASTNAME
ORDER BY P.GEOGRAPHY
What this is doing is returning me a distinct list of user names and countries who taught a course between the given dates. What I want to do is figure out the number of users for each country… for example if I have 2 Australian and 3 Canadian users the record set would look like
GEOGRAPHY COUNT FIRSTNAME LASTNAME
Australia 2 Blah Blah
Australia 2 What Ever
Canada 3 Some Guy
Canada 3 Who Knows
Canada 3 Just Me
I do have a query that looks like this…
SELECT COUNT(DISTINCT U.IDPERSON), P.GEOGRAPHY,
FROM USAGE U, PERSON P
WHERE U.IDPERSON = P.IDPERSON AND U.DISPLAY = 1 AND U.SUBMISSIONDATE > 'startdate' AND U.SUBMISSIONDATE < 'enddate'
GROUP BY P.GEOGRAPHY
ORDER BY P.GEOGRAPHY
And that will give me the proper number of users in the given time by their country, but I cannot get the names in there, if I add P.FIRSTNAME into the select I get counts of only 1.
I can’t for the life of me how to get a proper count in there though that doesn’t mess everything up… If anyone could give me a hand it would be much appreciated. I figure it’s got to be a join or union or something, but I can’t get it and I’m getting very frustrated by it…
Thanks!
–d