Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Group by - with two tables

    I am joining two tables USED and USERS and want to count the number of USES by each user

    Table USES (1 column - INITIALS)
    'AH'
    'PN'
    'PN'
    'JP'

    Table USERS (2 columns - INITIALS+USERNAME)
    'AH', 'Allan Hansen'
    'PN', 'Peter Nielsen'
    'JP', 'Julius Poulsen'

    The wanted result is a sorted view (containing 3 columns, INITIALS+USERNAME+COUNTS)
    'PN', 'Peter Nielsen', 2
    'AH', 'Allan Hansen', 1
    'JP', 'Julius Poulsen', 1

    How do I manage this?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It looks like a trivial join problem; what did you manage to do so far and why do you think it doesn't work? Would you, please, mind to post your query so that someone might assist you improve it?

  3. #3
    Join Date
    Jun 2003
    Posts
    81
    My Sql looks like:
    SELECT b.fornavn,l.os_user, count(l.os_user) antal
    FROM log_microstation l,bruger_detaljer b
    WHERE l.os_user = b.user_name(+)
    GROUP BY l.os_user
    ORDER BY antal DESC
    ;

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, your initial post's information doesn't really fit into the query you have posted in the previous message - table and column names differ. Could you, please, create a test case (CREATE TABLE and INSERT INTO a few sample records), rewrite a query so that it reflects those table and column names? Because, who is going to translate and compare different information on the fly? Besides, why do you expect anyone to do that?

    At the first glance, you "forgot" to include "b.fornavn" column into the GROUP BY clause.

  5. #5
    Join Date
    Mar 2008
    Posts
    89
    Out of the top of my head, if I ignore your posted SQL,
    how about something along these lines?

    Code:
    Select b.initials, b.username, count(a.initials)
    from uses a, users b
    where a.initials=b.initials
    group by b.initials, b.username
    order by 3 desc;
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    And how exactly is kfc@vd.dk supposed to learn something if you spoonfeed him?

  7. #7
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by Littlefoot
    And how exactly is kfc@vd.dk supposed to learn something if you spoonfeed him?
    Well I guess I got too excited about actually being able to be the guy who feeds for a change.

    However, we don't know if this was a piece of homework, or if he needs it urgently to get his system running.
    I'll try to be more educational next time.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

Posting Permissions

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