Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

Results 1 to 2 of 2

Thread: Can't figure out this damned SQL Query...

  1. #1
    dewey's Avatar
    239
    posts
    Run Megaman, Run

    Can't figure out this damned SQL Query...

    Hey Gang,

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

    Code:
    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

    Code:
    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...

    Code:
    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
    Last edited by dewey; January 9th, 2008 at 11:45 AM.

  2. #2
    Well. When you count the rows, you use the aggregate function count() which requires the "group by" part. The problem is that if you count the rows and group by nationality, you can't get the names of the users. If e.g. one row says "Australia | 5 |", which of those 5 person's name should be displayed? If one should display all of them, one would need theoretically thousands of columns just for names, and the database system does not support this.

    When you group by both geography and firstname (which you will have to do to put firstname in the select part), it counts users which lives in that certain country and has that firstname. So the count will always be 1, unless you have to users with the same firstname and "geography".

    It's not anything wrong with the join. It's about the logic of the sql statement. Make one query for the counting, and one query to get the names of the persons that you want to retrieve information about

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012