# Can't figure out this damned SQL Query

**URL:** <https://forum.kirupa.com/t/cant-figure-out-this-damned-sql-query/248547>\
**Category:** Uncategorized\
**Created:** [January 9, 2008, 3:32pm UTC](https://forum.kirupa.com/t/cant-figure-out-this-damned-sql-query/248547 "2008-01-09T15:32:51Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![dewey](https://avatars.discourse-cdn.com/v4/letter/d/919ad9/32.png) [@dewey](https://forum.kirupa.com/u/dewey)\
**Post date:** [January 9, 2008, 3:32pm UTC](https://forum.kirupa.com/t/cant-figure-out-this-damned-sql-query/248547/1 "2008-01-09T15:32:51Z")

</div>

Hey Gang,

I have been trying to figure out this query. This is what I have that works…

```auto
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

```auto

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…

```auto
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