By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,911 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,911 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
>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

P: n/a

"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

P: n/a

"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

P: n/a
"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

P: n/a
>> 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.