473,237 Members | 1,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

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 1823
>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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: | last post by:
gcc provides the libc which have more functions than standard C libraries. I saw in Windows API similar functions too, with identical parameters! So, I ask again: Why not, a committee specifies...
0
by: mjcsfo | last post by:
I can't seem to find a reference nor any helpful threads on this topic. I've gotten the following error in two circumstances: 1. A complex type has nested within it another complex type, in the...
3
by: Matthet | last post by:
Hello I've got simpleType restricted by pattern one element uses this simpleType and I would like another element to use this SimpleType but with one extra pattern. How to do it?
0
by: Andy Howden | last post by:
I'm trying to define a schema element (allPeriodData) containing up to 50 different decimal child elements (periodData). Each child element (periodData) identifies itself with an ID having a value...
4
by: scorpion | last post by:
I have a simple type like this: <xs:simpleType name="SizeType"> <xs:restriction base="xs:token"> <xs:enumeration value="small"/> <xs:enumeration value="medium"/> <xs:enumeration...
2
by: JJA | last post by:
Please advise on how to get the GROUP BY coded in an acceptable way: DECLARE @LO INT DECLARE @HI INT DECLARE @StartDate varchar(10) DECLARE @EndDate varchar(10) SELECT @StartDate =...
3
by: Bore Biko | last post by:
Dear, I don't have enought money to by a original Visual C++, so I use Visual C++ 6.0 Enterprise edition, this version doesen't have a HELP. Most of my friends programmers praise C++, as a...
6
by: hdante | last post by:
Out of curiosity, where are the group rules ? I ask so, because people are usually dismissed, by saying that something "is not standard". Thanks, Henrique Dante de Almeida
3
by: psuwebmasters | last post by:
I am doing some work developing a OneBox for a Google Mini. All I need to do is take a $_GET values from the Mini (in this case, specifically "query"), format it into a URI to pass off to another...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.