1
Guys, I have the following structure in a Mysql table:
I need to make a select that brings me as many posts as there are any records, for example, in reference 269 bring me to post 4, and in reference 258 bring me to post 6.
It may look like MAX, but bring me as many columns as you have any record of that reference. How can I select this?
1
Make a IF, if something is written add 1 but add 0. This IF will have to be done for each column, example:
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 is not with a suitable structure for a relational database, These posts, should be in another table with the reference of this table.
Yes, it’s just that I’m here on my PC at work, and this project I have separated in my notebook, then I came up with this question and I only simulated here quickly, but in my own project I have a table of references and separate posts, with Primary and Foreign Keys :)... and thank you for the reply, had not thought of this way, I will test and I answer you whether or not you answered my problem!
I took a test here and meets what I need! Thank you very much!!
Why the GROUP BY?
I didn’t see sense in group by either, I took it out
It’s just that I initially thought to do with SUM, but I changed my mind and GROUP BY ended up staying, I’ll withdraw.
Ah blz sahsuahsua
0
This query works in Mysql 8.x.
It brings the reference, the status of the post (content of the columns posto1..posto10) and the largest number of posts.
She ignores any gaps in these columns postoX and disregard discontinuities in these columns (for example, posto1 and posto3 empty and posto2 and posto4 filled - in case, the highest rank will be 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
-2
Count() with group by
select referencia, count() as qtd
from tabela
group by referencia
Browser other questions tagged mysql sql
You are not signed in. Login or sign up in order to post.
you have 10 columns to register each post, that’s it ?!
– Rovann Linhalis
That’s right.....
– maiconfriedel
If you sell your system one day, who knows, to an average industry that has 216 non-linear operations, you will create 206 extra columns and leave some of the middle empty??
– Marcelo Shiniti Uchimura
This system is now being developed specifically for a company with toy assembly sector, where the maximum number of stations is 20, and it is always linear... I am using this table as a basis to assemble for them the operational method, where will register the method and then view, with photo and description, because currently the company (as it is small and does not invest much in technology), records everything in excel spreadsheet, and there are no registered operational methods. But if you have any suggestions to help the table structure, I appreciate the suggestion! :)
– maiconfriedel