Select all columns greater than some value

debugcn Published at Dev

Dexter

I am new to mysql and I have structure like this

timestamp | Name | Total | Student1 | Student2 | Student3
20150911  | X    | 100   |       76 |       10 |       NA
20151111  | Y    | 70    |       NA |        0 |       23

Now I want to select with mysql query where at given 'timestamp'

all student columns which have non-zero numbers
all student columns which are NA
all student columns which are zero

Expected outcome

If I put 'non-zero' query , it should give names of column as ' Student1, Student2' for timestamp 20150911
If I put 'zero' query , it should give names of column as () for timestamp 20150911
If I put 'NA' query, it should give names of column as 'Student3' for timestamp 20150911

I have tried searching for such mixed selection but so far I am not successful. Most of answers are concentrating on row selection rather columns.

Lukasz Szozda

Your data structure is not normalized. But if you want to go this path use:

select sub.student
FROM (
  select t.timestamp,
    t.Name,
    t.Total,
    c.col AS student,
    case c.col
      when 'Student1' then Student1
      when 'Student2' then Student2
      when 'Student3' then Student3
      -- ...
    end as d
  from mytable t
  cross join
  (
    select 'Student1' as col
    union all select 'Student2'
    union all select 'Student3'
    -- ...
  ) c
) AS sub
WHERE sub.timestamp = '20150911'
  AND sub.d > 0;
  -- sub.d = 'NA'
  -- sub.d = 0

SqlFiddleDemo

Output:

╔══════════╗
║ student  ║
╠══════════╣
║ Student1 ║
║ Student2 ║
╚══════════╝

If you want comma separated result use:

select GROUP_CONCAT(sub.student ORDER BY sub.student) AS result

SqlFiddleDemo2

Output:

╔═══════════════════╗
║       result      ║
╠═══════════════════╣
║ Student1,Student2 ║
╚═══════════════════╝

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-15

Comments

0 comments

From Dev