Count or Account and add in a SELECT MySQL


A few days ago I solved a problemon to list a query result but stop the counter and start it again in the same query ...

Count or Account in a SELECT MySQL

in fact I wanted to reach this result (stopping at 4)

  • Carolina
  • Claudia
  • Daniel
  • Francisco
  • and there we restart ...

  • Jose
  • Maria
  • Mirta
  • Romina
  • That topic .. SOLVED! now I really wanted to record in another temporary column an identifier of each sequence or enumeration ...

  • Carolina .1
  • Claudia .1
  • Daniel .1
  • Francisco .1
  • and there we restart ... and the second cycle begins

  • Jose .2
  • Maria .2
  • Mirta .2
  • Romina .2
  • and so on ... can it be? I hope you can help me as they did before ..

    asked by Nano de los Santos 30.10.2018 в 20:17

    1 answer


    In the previous answer, Patricio made you make a Cartesian product between 2 tables, one with your data, and another with a column in 0, so that you could go iterating over it and go counting results.

    If we steal your idea, we can add another column ... and restart it only when the first pass 4 ... something like this:

    SELECT apellido,
       CAST(@s:= (CASE WHEN @s = 4 THEN 1 ELSE @s+1 END) AS UNSIGNED) AS '#',
       CAST(@t:= (CASE WHEN @s = 4 THEN @t+1 ELSE @t END) AS UNSIGNED) AS 't#'
       FROM mitabla,
       (SELECT @s:= 0) AS s,
       (SELECT @t:= 0) AS t
       WHERE   ciudad = "NEUQUEN" 
       ORDER BY apellido asc, nombre asc

    If the idea is correct, this would sum to t, every time s reaches 4 ...

    answered by 30.10.2018 / 20:53