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

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

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

>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

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

"Gordon Burditt" 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

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

"Gordon Burditt" wrote in message
news:121hjdvmd5cc539@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?

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

"Riled" wrote in message
news:sseSf.197044$YJ4.107731@fe2.news.blueyonder.co.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.

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

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