CODEWITHSUNDEEP
mysqlsqlgroup-by
ISURU UDAYANGA BANDARA
ISURU UDAYANGA BANDARA

Reputation: 73

GROUP BY ONLY HAVING different values same field

I want to use a query to Group by if that column value different only as an example I have a table like this

First Name   Last Name        DOB


Nimal        Herath         1987/03/15
Kamal        Bandara          Null
sunil        Perera         1988/08/20
Kasun        Sandaruwan     1985/07/02
Nimal        Herath           Null
Kamal        Bandara          Null
Sunil        Perera         1988/08/20
Kasun        Sandaruwan     1997/03/10

So I need to get users who have Same First Name and Last Name and Same Date of birth including Null DOB

When I use Group by It is return users Who have Same Value But It will Not Return If one user's DOB is null and other one will have a value

My Result should be as follow

First Name  Last Name  DOB      Count

Kamal        Bandara   Null        2
Sunil        Perera    1988/08/20  2
Nimal        Herath     1987/03/15 2

I tried This Query 

Select * From Users Group By FirstName,LastName,Dob HAVING Count(*) >1

This Will Return all Duplicate Users Who have same DOB and if both null but it will not return one have a dob and other one null

Upvotes: 1

Views: 1385

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You seem to want:

select FirstName, LastName, max(dob) as dbo, count(*)
from Users
group by FirstName, LastName 
having count(distinct dob) <= 1;

Here is a db<>fiddle.

Upvotes: 1

forpas
forpas

Reputation: 164064

You should group by FirstName, LastName only and set the conditions in the HAVING clause:

select 
  firstname, lastname, max(dob) dob, count(*) count
from users  
group by firstname, lastname
having 
  count(distinct dob) <= 1
  and
  count(*) > 1

See the demo.
Results:

| firstname | lastname | dob        | count |
| --------- | -------- | ---------- | ----- |
| Kamal     | Bandara  |            | 2     |
| Nimal     | Herath   | 1987/03/15 | 2     |
| sunil     | Perera   | 1988/08/20 | 2     |

Upvotes: 1

Zeshan
Zeshan

Reputation: 2657

Try this:


SELECT `FirstName`, `LastName`, `DOB`, count(*) as Count
FROM table
GROUP BY `FirstName`, `LastName`, `DOB`
HAVING count(*) > 1

Upvotes: 1

Related Questions