I have the following structure in a MySql table:
link
I need to make a select that brings me the maximum number of posts that have a record, for example, at reference 269, bring me to station 4, and at reference 258, bring me up to station 6.
It may be something like MAX, but bring me as many columns as you can with a record of that reference. How can I make this select?
Make a IF , if there is something written add 1 otherwise add 0. This IF will have to be done for each column, eg:
SELECT a.'REFERENCIA', a.'DESCRICAO', (
IF(a.'POSTO1' <> '', 1, 0) +
IF(a.'POSTO2' <> '', 1, 0) +
IF(a.'POSTO3' <> '', 1, 0) +
IF(a.'POSTO4' <> '', 1, 0) +
IF(a.'POSTO5' <> '', 1, 0) +
IF(a.'POSTO6' <> '', 1, 0) +
IF(a.'POSTO7' <> '', 1, 0) +
IF(a.'POSTO8' <> '', 1, 0) +
IF(a.'POSTO9' <> '', 1, 0) +
IF(a.'POSTO10' <> '', 1, 0)
) AS qtde
FROM nome_da_tabela a
This table does not have a structure suitable for a relational database, these posts should be in another table with the reference of this table.
This query works in MySQL 8.x.
It brings the reference, the status of the post (content of columns posto1 .. posto10 ) and the highest number of the post.
It ignores any voids that it has in these columns postoX and disregards discontinuities in those columns (for example, posto1 and posto3 empty and posto2 and posto4 filled - in this case, the highest rank will 4 ).
WITH q (referencia, statusposto, numposto) AS
(
SELECT referencia, posto1, 1
FROM tabela
WHERE posto1 IS NOT NULL AND posto1 <> ''
UNION
SELECT referencia, posto2, 2
FROM tabela
WHERE posto2 IS NOT NULL AND posto2 <> ''
UNION
...
UNION
SELECT referencia, posto10, 10
FROM tabela
WHERE posto10 IS NOT NULL AND posto2 <> ''
)
SELECT q.referencia, q.numposto maiorposto, q.statusposto
FROM q
JOIN (
SELECT q.referencia, MAX(q.numposto) maiorposto
FROM q
GROUP BY q.referencia) r ON r.referencia = q.referencia
AND r.maiorposto = q.numposto
count () with group by
select referencia, count() as qtd
from tabela
group by referencia