How to calculate the data correctly with the help of 'count' or perhaps in some other way data from the database?

There are 5 fields in the table, in the field there are 6 options for the values:

 1_day - Выходной, Рабочий, Отгул, Отпуск, Прогул, Больничный 2_day - Выходной, Рабочий, Отгул, Отпуск, Прогул, Больничный 3_day - Выходной, Рабочий, Отгул, Отпуск, Прогул, Больничный 4_day - Выходной, Рабочий, Отгул, Отпуск, Прогул, Больничный 5_day - Выходной, Рабочий, Отгул, Отпуск, Прогул, Больничный

How to calculate the number of days off in all fields?
Example:

 1_day = Выходной 2_day = Выходной 3_day = Рабочий 4_day = Выходной 5_day = Отгул

Closed due to the fact that off-topic participants jmu , Dmitriy Simushev , Denis , Bald , user194374 Aug 26 '16 at 8:24 .

It seems that this question does not correspond to the subject of the site. Those who voted to close it indicated the following reason:

  • “Questions asking for help with debugging (“ why does this code not work? ”) Should include the desired behavior, a specific problem or error, and a minimum code for playing it right in the question . Questions without an explicit description of the problem are useless for other visitors. See How to create minimal, self-sufficient and reproducible example . " - jmu, Dmitriy Simushev, Denis, Bald, Community Spirit
If the question can be reformulated according to the rules set out in the certificate , edit it .

    4 answers 4

    5 separate fields under the days - this is horror. There should be 5 records in a separate table. In your case, just something like that will help.

     select sum(if(`1_day`='Выходной',1,0)+ if(`2_day`='Выходной',1,0)+ ... if(`5_day`='Выходной',1,0) ) from table group by ... /* Если нужно */ 
       SELECT day, count(*) from table group by day

      only fields should be not 5, but one

         select * from table_name where (day) in (SELECT day FROM table_name group by day having count(*)>1)

        Request is looking for your number of duplicates

          badly grouped in rows and to be honest, I would take the Выходной, Рабочий, Отгул, Отпуск, Прогул, Больничный in a separate table and write id to the main (Any status Dikret or Business trip can be added) or make an ENUM field but it's bad add value will have to do this through ALTER TABLE . but we have what we have.

          Something like this I would do: SELECT CONUNT(day) as cnt_day FROM table GROUP BY 'day_status'

          • @DimitryKut guess the table structure then - Shadow33
          • @DmitryKut group by cf_2379. SELECT CONUNT(cf_2379) as cnt_day FROM table GROUP BY 'cf_2379' with such a structure without much difficulty you can make the ENUM field give it an index and get a free performance boost - Shadow33