CODEWITHSUNDEEP
sqlgroup-by
RNJ
RNJ

Reputation: 15562

SQL group by with a count

I have a table (simplified below)

|company|name |age|
| 1     |  a  | 3 | 
| 1     |  a  | 3 | 
| 1     |  a  | 2 | 
| 2     |  b  | 8 | 
| 3     |  c  | 1 | 
| 3     |  c  | 1 |

For various reason the age column should be the same for each company. I have another process that is updating this table and sometimes it put an incorrect age in. For company 1 the age should always be 3

I want to find out which companies have a mismatch of age.

Ive done this

select company, name age from table group by company, name, age

but dont know how to get the rows where the age is different. this table is a lot wider and has loads of columns so I cannot really eyeball it.

Can anyone help?

Thanks

Upvotes: 0

Views: 65

Answers (3)

geomagas
geomagas

Reputation: 3280

Since you mentioned "how to get the rows where the age is different" and not just the comapnies:

Add a unique row id (a primary key) if there isn't already one. Let's call it id.

Then, do

select id from table 
where company in 
    (select company from table 
    group by company 
    having count(distinct age)>1)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460258

If you want to find the row(s) with a different age than the max-count age of each company/name group:

WITH CTE AS
(
    select company, name, age,
           maxAge=(select top 1 age
             from dbo.table1 t2
             group by company,name, age
             having( t1.company=t2.company and t1.name=t2.name)
             order by count(*) desc)
    from dbo.table1 t1 
)
select * from cte 
where age <> maxAge

Demontration

If you want to update the incorrect with the correct ages you just need to replace the SELECT with UPDATE:

WITH CTE AS
(
    select company, name, age,
           maxAge=(select top 1 age
             from dbo.table1 t2
             group by company,name, age
             having( t1.company=t2.company and t1.name=t2.name)
             order by count(*) desc)
    from dbo.table1 t1 
)
UPDATE cte SET AGE = maxAge 
WHERE age <> maxAge

Demonstration

Upvotes: 0

John Woo
John Woo

Reputation: 263883

You should not be including age in the group by clause.

SELECT  company
FROM    tableName
GROUP   BY company, name
HAVING  COUNT(DISTINCT age) <> 1

Upvotes: 3

Related Questions