CODEWITHSUNDEEP
mysqljoinsubquerycorrelated-subquery
IanCun
IanCun

Reputation: 47

Join or SubQuery?

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.

Upvotes: 0

Views: 68

Answers (6)

D Mayuri
D Mayuri

Reputation: 456

WITH cte
AS
(
    SELECT t.AddonName, MAX(t.Version) AS latest_version 
    FROM Table1 t 
    GROUP BY t.AddonName 
)
SELECT t.AddonName, COUNT(t.WebsiteID) AS total_addons,
SUM
(
    CASE WHEN t.version = cte.latest_version 
    THEN 1 ELSE 0 END
) AS up_to_date,
SUM
(
    CASE WHEN t.version <> cte.latest_version 
    THEN 1 ELSE 0 END
) AS out_of_date
FROM Table1 t 
JOIN cte ON t.AddonName = cte.AddonName 
GROUP BY t.AddonName

Upvotes: 0

Pankaj Monga
Pankaj Monga

Reputation: 179

try this this will solve your problem.

select AddonName,count(AddonName) as countAdd,(select count(Version)from test1 as t where  t.AddonName = test1.AddonName and t.Version = max(test1.Version)),(select count(Version) from test1 as t where t.AddonName = test1.AddonName and t.Version = min(test1.Version))from test1 GROUP BY AddonName;

Upvotes: 1

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

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

Upvotes: 2

user5226582
user5226582

Reputation: 1986

Once you add that Latest table, try following (after inserting your table names)

select 
  AddonName as Name, 
  count(*) as TotalAddons,
  count(case TableName.Version when Latest.LatestVersion 
    then 1 else null end) as UpToDate,
  TotalAddons-UpToDate as OutOfDate

from TableName join Latest 
  on TableName.AddonName = Latest.AddonName 
group by AddonName

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Assuming the latest version is from the last column:

select t.name, count(*) as TotalAddons,
       sum(t.version = tt.maxv) as UpToDate,
       sum(t.version <> tt.maxv) as OutOfDate
from t join
     (select name, max(version) as maxv
      from t
      group by name
     ) tt
     on t.name = tt.name
group by t.name;

This calculates the maximum version number for each name in a subquery. It then uses that information for the outer aggregation.

This assumes that version is a number. If it is a string (so 1.10 > 1.2), then a similar approach is:

select t.name, count(*) as TotalAddons,
       sum(t.version = t.maxv) as UpToDate,
       sum(t.version <> t.maxv) as OutOfDate
from (select t.*,
             (select version
              from t t2
              where t2.name = t.name
              order by length(version) desc, version desc
              limit 1
             ) as maxv
      from t
     ) t
group by t.name;

Of course, this will also work for numbers as well.

Upvotes: 1

Inpego
Inpego

Reputation: 2667

Try:

SELECT your_table.AddonName,
   COUNT(`Website ID`),
   COUNT(IF(Version = your_table_max.max_version, 1, NULL)) AS `Up to Date`,
   COUNT(IF(Version <> your_table_max.max_version, 1, NULL)) AS `Out of Date`
  FROM your_table
    INNER JOIN (SELECT MAX(Version) as max_version, AddonName
                  FROM your_table group by AddonName) your_table_max
    ON your_table_max.AddonName = your_table.AddonName
  GROUP BY your_table.AddonName;

Upvotes: 1

Related Questions