Select in each answer different values with the Bank MySql

2

I would like to know how to select the number of records in a table where only values greater than 6 are selected, but in each result it would be different with the MySql database, for example:

Tabela Notas
Unidade I    |  Unidade II    |  Unidade III   | Unidade IV
10           |  4             |  7             | 3
1            |  3             |  7             | 9
7            |  0             |  8             | 1

Where values greater than 6 will appear like this in the query;

Unidade I    |  Unidade II    |  Unidade III   | Unidade IV
2 Registros  |  0 Registros   |  3 Registros   | 1 Registro
    
asked by anonymous 02.09.2017 / 18:34

3 answers

1

Functional code:

select
count(case when 'Unidade I' > 6 then 0 end) as 'Unidade I',
count(case when 'Unidade II' > 6 then 0 end) as 'Unidade II',
count(case when 'Unidade III' > 6 then 0 end) as 'Unidade III',
count(case when 'Unidade IV' > 6 then 0 end) as 'Unidade IV'
from Notas
  

Note that if the field name in the table has space , you should   refer to it as "

02.09.2017 / 18:51
0

I was able to solve this problem, as below

SELECT 
COUNT(CASE WHEN nota_valorI>6 then 1 ELSE NULL END) as "UNI_I",
COUNT(CASE WHEN nota_valorII>6 then 1 ELSE NULL END) as "UNI_II",
COUNT(CASE WHEN nota_valorIII>6 then 1 ELSE NULL END) as "UNI_III",
COUNT(CASE WHEN nota_valorIV>6 then 1 ELSE NULL END) as "UNI_IV"
from nota
    
02.09.2017 / 18:47
0
SELECT * FROM
(

SELECT 'Unidade I' AS UN FROM NOTAS
UNION 
SELECT 'Unidade II' AS UN  FROM NOTAS
UNION
SELECT 'Unidade III'AS UN  FROM NOTAS
UNION
SELECT 'Unidade IV' AS UN  FROM NOTAS
) AS TA

WHERE UN  > 6
    
02.09.2017 / 19:43