470,647 Members | 1,248 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

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
Mar 16 '06 #1
5 1729
>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

"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

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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Matthet | last post: by
3 posts views Thread by Bore Biko | last post: by
6 posts views Thread by hdante | last post: by
3 posts views Thread by psuwebmasters | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.