Join or SubQuery?

Question

I've got a MySQL table that records the addon titles used on various websites, including a version number. For example:

    AddonName | Website ID | Version

    ZZZ         1           3.3
    ZZZ         2           3.4
    ZZZ         3           3.4
    ZZZ         4           3.1
    YYY         1           1.1
    YYY         2           1.1
    YYY         3           1.1
    YYY         4           1.2

I'd like to create a query that lists a distinct list of AddonName, with details of the total count, count of all sites using the latest version, and counts of all sites using out of date versions.

i.e.:

    Name | Total Addons | Up to Date | Out of Date
    ZZZ    4              2            2
    YYY    4              1            3

I can't figure out how to get this type of data returned, even though the information is all there. I tried using JOIN queries, but didn't have any success.

If it helps make things easier, I can add a 'latest' enum field to the table, to mark rows as up-to-date or out-of-date when the are imported.

Answer 1

Assuming max value as latest version.

Try this:

select t1.AddonName,
count(*) as total_Addon,
sum(case when t1.version=t2.version then 1 else 0 end) as up_to_date,
sum(case when t1.version!=t2.version then 1 else 0 end) as out_of_date
from table1 t1
inner join(
  select AddonName,max(version) as version
  from table1 group by AddonName
)t2 on t1.AddonName=t2.AddonName 
group by t1.AddonName