Home>

When there is a table like image 1, count the number of strings in the same line,
Is it possible to create a column in SQL?
In the image 1, it is an image that you can add numbers to each orange part.

・ Is such processing possible with SQL in the first place?
・ What kind of function should I use if processing is possible?

If i am familiar with SQL, please let me know.

Thank you very much.

  • Answer # 1

    I think that it is possible to try to do it using the CASE expression.

    In the first place, I think it's a trick to do tricky things like "Can you do this?"
    I do this.

    Lottery result code master

    Code (PK) Name
    0 No participation
    1 Join
    2 Winning

    Lottery

    User ID (PK) times (PK) Lottery result code
    A 1 1
    A 2 2
    B 1 2
    B 2 0
    : : :
    F 2 1

    Kujibiki summary (if you really need persistence)

    User ID (PK) Non-participation count Participation count Winning count
    A 0 2 1
    : : : :

    If you design in this way, you can count the number of records just by getting the number of records narrowed down by the user and the lottery result code from the lottery table.
    If you update or insert with something calledSELECT UPDATEorINSERT ... SELECT.

  • Answer # 2

    All you need to do is add a condition.

    select user
          , case Lottery first time when 'Non-participation' then 1 else 0 end
          + case 2nd lottery when 'not participated' then 1 else 0 end as
          , case 1st draw when 'participating' then 1 else 0 end
          + case 2nd draw when 'participate' then 1 else 0 end as total draws
          , case 1st draw when 'winning' then 1 else 0 end
          + case 2nd draw when 'winning' then 1 else 0 end as lottery wins
    from table


    Another pattern.

    select user
          , sum (case lottery result when 'not participated' then 1 end) as
          , sum (case lottery result when 'participation' then 1 end) as total number of lottery participations
          , sum (case lottery result when 'winning' then 1 end) as total number of lottery wins
    from (
                  select User, 1st draw as lottery result, 1 as lottery from table
        union all select user, second draw, 2 from table
    ) as t
    group by user

    The table structure is not normalized anyway, so it is inevitable that it becomes a troublesome description.