How to Mysql count only higest values per row

[stav]

I have 2 tables and I want to count highest version per country for all users

Example:

John is from the USA, and he has versions 1,2,3,4,5 and 6

Bob is from France, and he has versions 1,2 and 3

Mary is from France too, and she has versions 1,2 and 3

I want to count just the last versions from anny of them

Becouse :

John has higher version 6 he won't be counted in versions lower than 6

Bob has higher version 3 he won't be counted in versions lower than 3

Mary has higher version 3 she won't be counted in versions lower than 3

So the final result will be as the Desired result set at bottom

Table1
ID | USER  |  COUNTRY
---- ----- ----------
1  |  John |  USA
2  |  Bob  |  FRANCE
3  |  Mary |  FRANCE



Table2
ID |  USER  | VERSION
---- ------- ---------
1  |  John  |  1
2  |  Bob   |  1 
3  |  John  |  2 
4  |  Mary  |  1 
5  |  Mary  |  2 
6  |  John  |  3 
7  |  John  |  4 
8  |  Bob   |  2 
9  |  John  |  5 
10 |  Bob   |  3 
11 |  Mary  |  3 
12 |  John  |  6 

 

Desired result set:

Country | V1| V2 | V3 | V4 | V5 | V6
-------- ---- --- ---- ---- ---- ----
USA     | 0 | 0  | 0  |  1 | 1  |  1
FRANCE  | 0 | 0  | 2  |  0 | 0  |  0

 

Please help me , thanks.

[Barand]

Use a table subquery to calculate the maximum version for each user, then count those by country/varsion.

[stav]

I would greatly appreciate it if you could help me solve this oneI would be grateful if you could/would write a simple example on how to acomplish that

Thaks sir

[stav]

3 hours ago, stav said:

There was typo

Desired result set:

Country | V1| V2 | V3 | V4 | V5 | V6
-------- ---- --- ---- ---- ---- ----
USA     | 0 | 0  | 0  |  0 | 0  |  1
FRANCE  | 0 | 0  | 2  |  0 | 0  |  0

 

[Barand]

The subquery calcultaing the maximum scores .behaves just like a temporary table. So you can the join it to tbale using user and count by country.

SELECT a.country
     , sum(b.version=1) as V1
     , sum(b.version=2) as V2
     , sum(b.version=3) as V3
     , sum(b.version=4) as V4
     , sum(b.version=5) as V5
     , sum(b.version=6) as V6
FROM table1 a
     JOIN (
            SELECT user
                 , MAX(version) as version
            FROM table2
            GROUP BY user
          ) b USING (user)
GROUP BY country
ORDER BY country DESC;
+---------+------+------+------+------+------+------+
| country | V1   | V2   | V3   | V4   | V5   | V6   |
+---------+------+------+------+------+------+------+
| USA     |    0 |    0 |    0 |    0 |    0 |    1 |
| France  |    0 |    0 |    2 |    0 |    0 |    0 |
+---------+------+------+------+------+------+------+

 

[Barand]

The subquery calculating the maximum scores .behaves just like a temporary table. So you can the join it to table1 using user and count by country.

SELECT a.country
     , sum(b.version=1) as V1
     , sum(b.version=2) as V2
     , sum(b.version=3) as V3
     , sum(b.version=4) as V4
     , sum(b.version=5) as V5
     , sum(b.version=6) as V6
FROM table1 a
     JOIN (
            SELECT user
                 , MAX(version) as version
            FROM table2
            GROUP BY user
          ) b USING (user)
GROUP BY country
ORDER BY country DESC;
+---------+------+------+------+------+------+------+
| country | V1   | V2   | V3   | V4   | V5   | V6   |
+---------+------+------+------+------+------+------+
| USA     |    0 |    0 |    0 |    0 |    0 |    1 |
| France  |    0 |    0 |    2 |    0 |    0 |    0 |
+---------+------+------+------+------+------+------+

Quote

There was typo

I know

Edited October 3, 2021 by Barand

[stav]

Super cool and clean how you write that

Thank you very much sir