Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Query - Group By

    Hi,

    I have a database with thousands of records in. The records are about customers, of which some have identical names. I would like to return the first name and surname and count of people who have the same name in the database.

    For example there may be 4 'Joe Bloggs'

    The results would be

    FirstName SurName Count
    Joe Bloggs 4

    How can this be done using group by?

    Thanks

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Do you just want to know how many records contain the same name? Why would you want to use Group By?

    Just use the count() function and select just the records where the name = the name you are looking for.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There may be 50 people with the same name....


    so I want to produce a list of these people and their count....

  • #4
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    Try this:
    SELECT firstname + ' ' + lastname As fullname,COUNT(fullname) AS numberPeople FROM databasename GROUP BY fullname

    if that does not work try this one.....

    SELECT COUNT(firstname) AS numberPeople ,firstname + ' ' + lastname AS fullname FROM databasename GROUP BY fullname

    Not positive it will work, but worth a try

    Eric

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a few problems - I'm working on an oracle database and when I do a:

    SELECT FirstName + ' ' + SurName AS NoOfPeople FROM tblCustomer

    I get:


    ORA-01722: invalid number


    It doesn't seem to like how its concatinated......

    any ideas?

  • #6
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    concatenating is different with Oracle.....

    instead of the plus use ||

    a || ' ' || b

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks!

    I have got the script working except I need to return where the count is greater than 1 (i.e. where more than 1 person with the same full name exists)

    Heres my code:

    SELECT COUNT(FirstName|| ' '|| SurName), FirstName || ' ' || SurName FROM tblCustomer GROUP BY FirstName || ' ' || SurName


    I know the code may be rubbish but this query is only a 1 off!!

    When I try:

    SELECT COUNT(FirstName|| ' '|| SurName), FirstName || ' ' || SurName FROM tblCustomer WHERE COUNT(FirstName|| ' '|| SurName) > 1 GROUP BY FirstName || ' ' || SurName

    I get:

    ORA-00934: group function is not allowed here


    Any ideas?

  • #8
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    try

    SELECT COUNT(FirstName|| ' '|| SurName), FirstName || ' ' || SurName FROM tblCustomer HAVING COUNT(FirstName|| ' '|| SurName) > 1 GROUP BY FirstName || ' ' || SurName

    If you want to learn more about SQL in a great easy to use format:
    I recommend SAMS Teach Yourself SQL in 10 Minutes...The book cost around $15 and is the best reference for SQL I ever found. Shows sytax for ORACLE and other Databases.

    I use the book on a daily basis since I hate to memorize certain rules...

    Eric

  • #9
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Eric - magic! thanks very much for your help!

    Good tip too, I will have a look at the book you mentioned!

  • #10
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by A1ien51
    more about SQL in a great easy to use format:
    I recommend SAMS Teach Yourself SQL in 10 Minutes...The book cost around $15 and is the best reference for SQL I ever found. Shows sytax for ORACLE and other Databases.

    I use the book on a daily basis since I hate to memorize certain rules...

    Eric
    just ordered it on amazon... thanx for reference I've benn looking for a book on sql topics for a while but I always got confused 'cause there are so many... But I totally trust you
    Well... sorry for hi-jacking this thread and off-topicing

  • #11
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    The book is very down to the point, It is small and cheap, has great examples....

    I do book reviews for publishers and I would say that is one of the best references that I use. I bought that book on a whim while buying a VB.Net database programming book and a ASP book. Out of the three I only use the SQL book.
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #12
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    talking about books.... (this will be my last post about it I swear...) is the sql bible a good reference? http://www.amazon.com/exec/obidos/tg...books&n=507846
    When there is something I have a doubt about I usually use that link: http://www.w3schools.com/sql/default.asp but it's kind of limited... Would you have a link that you would recommend to a more developped sql site?

  • #13
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    I have not used the SQL Bible, but most of the bible books are really good.

    For SQL online, I really just search GOOGLE for things, I can not find a great reference that covers everything. Plus I use my 10 minute book for most of my questions.

    I currently am deveolping a database managment program, it is going slowly since I have way too much development work at work.

    Eric
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #14
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok... thanx a lot Eric


  •  

    Posting Permissions

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