Thursday, 30 August 2018

MySQL query help - 'count' as variable does not work as expected


I have the following (fairly complex) query:


SELECT
  @idx :=
    CASE
      WHEN @prev_paper = paper_id
        THEN @idx +1
      ELSE 1
    END AS idx,
@prev_paper := t1.paper_id AS paper_id,
@cnt := (SELECT COUNT(DISTINCT(organization)) as dcnt from authors A INNER JOIN authors__papers AP on AP.author_id = A.author_id where AP.is_contact_author < 1 AND paper_id = @prev_paper GROUP BY paper_id) as org_count,
IF(@cnt > 1, GROUP_CONCAT('{', @idx, '}', first_name, last_name), GROUP_CONCAT(first_name, last_name)) AS names
FROM (
SELECT
AP.paper_id as paper_id, A.organization, A.first_name, A.last_name, A.country
FROM authors__papers AP
INNER JOIN authors A ON A.author_id = AP.author_id
WHERE AP.is_contact_author <1
) AS t1, (
SELECT @prev_paper :=  '', @idx :=0
) AS t2
GROUP BY paper_id, organization
ORDER BY paper_id, organization

And it outputs results as follows:
idx paper_id    org_count   names
1   5002        2           MarioIannazzo,EduardAlarcon
2   5002        2           {2}VikramPassi,{2}HimadriPandey,{2}MaxLemme
1   5003        1           {1}JiaSun
1   5004        1           Juan A.Leñero-Bardallo,AngelRodríguez-Vázquez,RicardoCarmona-Galán
1   5005        3           AlexandreVernhet,JeanCoignus
2   5005        3           {2}GerardGhibaudo
3   5005        3           {3}Jean-LucOgier,{3}GiulioTorrente,{3}DavidRoy
1   5006        1           {1}JerodMason,{1}PaulDicarlo,{1}HanchingFuh,{1}DavidWhitefield,{1}FlorinelBalteanu
1   5007        3           SivkhengKor,DavidSchwartz,JanosVeres,PingMei
2   5007        3           {2}ChristerKarlsson,{2}PerBroms
3   5007        3           {3}Tse NgaNg
...

As you can see, 'org_count' (@cnt) is not working as expected. '@idx' is not appended to the names sometimes when it should be because it's > 1 (like 5002) and is sometimes when it is not expected to be because it is = 1 (like 5003, 5006 ...). This should look like:
idx paper_id    org_count   names
1   5002        2           {1}MarioIannazzo,{1}EduardAlarcon
2   5002        2           {2}VikramPassi,{2}HimadriPandey,{2}MaxLemme
1   5003        1           JiaSun
1   5004        1           Juan A.Leñero-Bardallo,AngelRodríguez-Vázquez,RicardoCarmona-Galán
1   5005        3           {1}AlexandreVernhet,{1}JeanCoignus
2   5005        3           {2}GerardGhibaudo
3   5005        3           {3}Jean-LucOgier,{3}GiulioTorrente,{3}DavidRoy
1   5006        1           JerodMason,PaulDicarlo,HanchingFuh,DavidWhitefield,FlorinelBalteanu
1   5007        3           {1}SivkhengKor,{1}DavidSchwartz,{1}JanosVeres,{1}PingMei
2   5007        3           {2}ChristerKarlsson,{2}PerBroms
3   5007        3           {3}Tse NgaNg
    ...

It's like something seems off by 1, but I cannot for the life of me figure out what or why. Any help is appreciated!

0 comments:

Post a Comment