How to count equal items from 5 different columns?

Asked

Viewed 74 times

1

I have a table thus. The table keeps the attendance record ( P) and fouls ( F). I need to count, for example, how many presences and how many missing a certain code has.

mysql

  • Did you also pass the trial in question? You could do so oh... presenca -> id, idUsuario, dia, presenca ... that way, every time you have confirmed presence, registers in the database, and you just make a group by idUsuario and a presenca Where = 1 (present) 2 (missed) and can know the amount...

  • This table monitor only uses week? of five days? or you want to monitor the whole month?

1 answer

2

I’d do it this way:

Table structure.

CREATE TABLE IF NOT EXISTS `frequencia` (
  `idFrequencia` int(10) NOT NULL AUTO_INCREMENT,
  `idUsuario` int(10) NOT NULL,
  `diaSemana` int(10) NOT NULL,
  `presenca` int(10) NOT NULL,
  PRIMARY KEY (`idFrequencia`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `frequencia` 
(`idFrequencia`, `idUsuario`, `diaSemana`, `presenca`) 
VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 1, 3, 2),
(4, 1, 4, 2),
(5, 1, 5, 1);

And then I’d make an SQL:

SELECT count(idFrequencia) as qtdFalta FROM frequencia WHERE idUSuario = '1' GROUP BY presenca = '2';

To get the results. It is an option!

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.