CODEWITHSUNDEEP
mysqlsqlcountsql-update
Jean-Denis Muys
Jean-Denis Muys

Reputation: 6852

MySQL: count records by value and then update another column with their count

Three very related SQL questions (I am actually using mySQL):

Assuming a people table with two columns name, country

1) How can I show the people who have fellow citizens? I can display the count of citizens by country:

select country, count(*) as fellow_citizens 
from people 
group by country

But I can't seem to show those records for which fellow_citizens is > 1. The following is invalid:

select name, country, count(*) as fellow_citizens 
from people 
group by country 
where fellow_citizens > 1;

... and would not be what I want any way since I don't want to group people.

2) To solve the above, I had the idea to store fellow_citizens in a new column. This sounds like a variation on questions such as MySQL: Count records from one table and then update another. The difference is that I want to update the same table.

However the answer given there doesn't work here:

update people as dest 
set fellow_citizens = 
    (select count(*) from people as src where src.country = dest.country);

I get this error message:

You can't specify target table 'dest' for update in FROM clause

It seems like I need to through another temporary table to do that. Can it be done without a temporary table?

3) As a variant of the above, how can I update a column with a people counter by country? I found I can update a global counter with something like:

set @i:=0; update people set counter = @i:=@i+1 order by country;

But here I would like to reset my @i counter when the value of country changes.

Is there a way to do that in mySQL without going to full-blown procedural code?

Upvotes: 2

Views: 2012

Answers (4)

Johan
Johan

Reputation: 76753

Your select query should look something like:

SELECT name, country, count(*) as fellow_citizens  
FROM people  
GROUP BY country  
HAVING fellow_citizens > 1;

Recommended solution
You don't want to group, you just want to repeat the count for every person.
This data is not normalized and code like this is not a great way to do things.
However the following select will give you all the people and their counts: 

SELECT p.* 
     , s.fellow_citizens
FROM people p
INNER JOIN (
  SELECT country, count(*) as fellow_citizens
  FROM people  
  GROUP BY country  
  HAVING count(*) > 1) s ON (s.country = p.country)

If you don't want the actual count, just people from countries with more than 1 citizen, another way to write this query is (this may be faster than previous method, you have to test with your data):

SELECT p.* 
FROM people p
WHERE EXISTS
      ( SELECT *
        FROM people p2  
        WHERE p2.country = p.country 
          AND p2.PK <> p.PK         -- the Primary Key of the table 
      )

Only do this if you are facing slowness
If you want to include the counts into the table using a update statement I suggest you use a separate count table, because at least that's somewhat normalized. 

INSERT INTO people_counts (country, pcount)
  SELECT p.country, count(*) as peoplecount
  FROM people p
  GROUP BY p.country
ON DUPLICATE KEY pcount = VALUES(pcount)

Then you can join the counts table with the people data to speed up the select. 

SELECT p.*, c.pcount
FROM people p
INNER JOIN people_counts c ON (p.country = c.country)

Upvotes: 2

Hugh Jones
Hugh Jones

Reputation: 2694

I would do this.

create view citizencount as
select country, count(*) citizens 
from people 
group by country

Then your Query becomes (Something like)

select p.personid, p.country, cc.citizens -1 fellow_citizens
from people p
join citizencount cc on
  cc.country = p.country

You might then want to add

where fellow_citizens > 0

Upvotes: 1

Naved
Naved

Reputation: 4138

Have you tried this for both of your problem 

select name, fellow_citizens
from people left join  
(select country, count(*) as fellow_citizens  
 from people group by country  
 having fellow_citizens > 1) as citizens  
 on people.country = citizens.country

I am not very good at writing sql query; but I think this can solve your problem

Upvotes: 0

Zohaib
Zohaib

Reputation: 7116

1 -> 

select country, count(*) as fellow_citizens 
from people 
group by country 
having fellow_citizens > 1

Sorry, but could not really understand points 2 and 3.

Upvotes: 1

Related Questions