There are 2 tables Table1 and Table2 , both contain the same columns, say, Last Name , First Name , Middle Name .

It is necessary to count how many times each Surname occurs in each of the tables, then get the difference between 2 tables and display 10 Surnames, for which the difference between the references in the tables is greatest. Thank you in advance.

    1 answer 1

    If mysql, then with subqueries, not PG, as I wrote initially, then something like this should work out .. I can not vouch for the syntax, I wanted to say a general idea // everything is OK with the syntax

    SELECT ABS(t1.ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ - t2.ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ) AS 'Π Π°Π·Π½ΠΈΡ†Π°', t1.Ѐамилия FROM ( SELECT COUNT(1) AS 'ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ', Ѐамилия FROM Π’Π°Π±Π»ΠΈΡ†Π°1 GROUP BY "Ѐамилия" ) AS t1 INNER JOIN ( SELECT COUNT(1) AS 'ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ', Ѐамилия FROM Π’Π°Π±Π»ΠΈΡ†Π°2 GROUP BY "Ѐамилия" ) AS t2 ON (t1.Ѐамилия = t2.Ѐамилия) ORDER BY "Π Π°Π·Π½ΠΈΡ†Π°" DESC LIMIT 10
    • When did this appear in MySQL WITH ? And it's better to take abs from the difference. It can be both positive and negative - Mike
    • I understood the point, but since there is no with, it needs to be implemented somehow through subqueries, but tables are obtained as subqueries (which cannot be subtracted), not separate columns of the count - PPV