Maybe simple - group by / having restriction - but then show all results.

David Smithz

Hi there,

From a customer database table, I need to get a list of all the customers
that have the same birthdays and their names.

While I can get a count of this and the name of one of the customers of the
group sharing the same birthday by using the following code:

SELECT ALL count(dob) AS DOBcount, dob, firstname, lastname
FROM accounts
GROUP BY dob
HAVING DOBcount > 1

example output...
2, 1960-1-1 , john, smith
3, 1961-2-1, maggie, johnson

This only gives me one name from the list of people who share the same
birthdate. So in the above example we can see that two people in the DB are
born on 1960-1-1, but we cannot see who the other one is?

Is there a way to make the SQL, only show the groups having a count of more
then 1 for sharing a birthday, but when it does show these, it lists them
all?

Kind regards
Dave

Mar 16 '06 #1

Gordon Burditt

>From a customer database table, I need to get a list of all the customers

that have the same birthdays and their names.

While I can get a count of this and the name of one of the customers of the
group sharing the same birthday by using the following code:

SELECT ALL count(dob) AS DOBcount, dob, firstname, lastname ^^^ what does the word ALL mean here?FROM accounts
GROUP BY dob
HAVING DOBcount > 1

example output...
2, 1960-1-1 , john, smith
3, 1961-2-1, maggie, johnson

This only gives me one name from the list of people who share the same
birthdate. So in the above example we can see that two people in the DB are
born on 1960-1-1, but we cannot see who the other one is?

How about:

SELECT count(dob) as DOBcount, dob,
group_concat(concat(firstname, ' ', lastname))
FROM accounts
GROUP BY dob
HAVING DOBcount > 1;

Gordon L. Burditt

Mar 16 '06 #2

Riled

"Gordon Burditt" <go***********@burditt.org> wrote in message

How about:

SELECT count(dob) as DOBcount, dob,
group_concat(concat(firstname, ' ', lastname))
FROM accounts
GROUP BY dob
HAVING DOBcount > 1;

Tried this and got:
You have an error in your SQL syntax near ' group_concat( concat( firstname,
' ', lastname ) )

I also then just tried
group_concat(lastname)

but got a similar error.
Can you let me know how I should format this?

Thanks

Mar 16 '06 #3

Riled

"Gordon Burditt" <go***********@burditt.org> wrote in message
news:12*************@corp.supernews.com...

How about:

SELECT count(dob) as DOBcount, dob,
group_concat(concat(firstname, ' ', lastname))
FROM accounts
GROUP BY dob
HAVING DOBcount > 1;

Ahhh, It would seem that GROUP_CONCAT is only available after MySQL Version
4.1 and my host provides 4.0. That is why I was getting the error.

So is there any alternative to what I asked to get not using GROUP_CONCAT?

Mar 16 '06 #4

Bill Karwin

"Riled" <da******@blueyonder.co.uk> wrote in message
news:ss*********************@fe2.news.blueyonder.c o.uk...

Ahhh, It would seem that GROUP_CONCAT is only available after MySQL
Version 4.1 and my host provides 4.0. That is why I was getting the error.

So is there any alternative to what I asked to get not using GROUP_CONCAT?

SELECT a1.dob, a1.firstname, a1.lastname, a2.firstname, a2.lastname
FROM accounts AS a1 INNER JOIN accounts AS a2
ON a1.dob = a2.dob AND a1.primaryKey < a2.primaryKey;

Then do some post-processing in your application code, in case there are
more than two people who share a given dob.

Regards,
Bill K.

Mar 16 '06 #5

Gordon Burditt

>> SELECT count(dob) as DOBcount, dob,

group_concat(concat(firstname, ' ', lastname))
FROM accounts
GROUP BY dob
HAVING DOBcount > 1;

I tested this query with a different table and different field names.
It worked under MySQL 5.0.18.
Tried this and got:
You have an error in your SQL syntax near ' group_concat( concat( firstname,
' ', lastname ) )

I also then just tried
group_concat(lastname)

but got a similar error.
Can you let me know how I should format this?

With MySQL 4.1 or later.

Gordon L. Burditt

Mar 16 '06 #6