...

View Full Version : SQL Query - Group By



holty
11-05-2003, 02:22 PM
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

Spookster
11-05-2003, 02:57 PM
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.

holty
11-05-2003, 03:12 PM
There may be 50 people with the same name....


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

A1ien51
11-05-2003, 05:53 PM
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

holty
11-06-2003, 11:04 AM
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?

A1ien51
11-06-2003, 09:50 PM
concatenating is different with Oracle.....

instead of the plus use ||

a || ' ' || b

holty
11-07-2003, 08:41 AM
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?

A1ien51
11-07-2003, 07:47 PM
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

holty
11-10-2003, 08:50 AM
Eric - magic! thanks very much for your help!

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

jeskel
11-11-2003, 10:53 PM
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 :o

A1ien51
11-11-2003, 11:37 PM
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.

jeskel
11-12-2003, 09:46 AM
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/detail/-/0764525840/qid=1068630260//ref=sr_8_xs_ap_i0_xgl14/103-5588693-7323831?v=glance&s=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?

A1ien51
11-13-2003, 01:19 AM
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

jeskel
11-13-2003, 11:00 AM
ok... thanx a lot Eric :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum