A complex (so I believe) DISTINCT COUNT query case

Hello all,

In this table “d” I have all the challenges:

CREATE TABLE `desafios` (
 `cod_desafio` int(11) NOT NULL AUTO_INCREMENT,
 `titulo` varchar(255) NOT NULL,
 `descricao` text NOT NULL,
 `pontos` int(11) NOT NULL,
 `cod_categoriaFk` int(11) DEFAULT NULL,
 `recorrente` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`cod_desafio`),
 KEY `cod_categoriaFk` (`cod_categoriaFk`),
 KEY `pontos` (`pontos`),
 CONSTRAINT `desafios_ibfk_1` FOREIGN KEY (`cod_categoriaFk`) REFERENCES `categorias` (`cod_categoria`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8

In this one “e” I store the teams:

CREATE TABLE `equipas` (
 `cod_equipa` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(100) NOT NULL,
 `pontos` int(11) NOT NULL,
 PRIMARY KEY (`cod_equipa`),
 UNIQUE KEY `nome` (`nome`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

I connect them by using a third table “de” that has the challengesbyteam

CREATE TABLE `desafiosporequipa` (
 `cod_desafio_equipa` int(11) NOT NULL AUTO_INCREMENT,
 `cod_equipaFk` int(11) DEFAULT NULL,
 `cod_desafioFk` int(11) DEFAULT NULL,
 `estado` tinyint(1) NOT NULL COMMENT '0| Pendente 1|concluido 2|recusado',
 `prova` varchar(255) NOT NULL,
 `data_concluido` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data da conclusao do desafio',
 `anexo` varchar(255) DEFAULT NULL,
 `bonus` int(11) DEFAULT NULL,
 PRIMARY KEY (`cod_desafio_equipa`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8

I would like to COUNT all the challenges that are with de.estado = 1 from a given team.

However, and here enters the hard part, some challenges can be repeated (those that have “d.recorrente = 1” ), and I would like to COUNT only 1 occurrence of those.
So that it can COUNT how many complete distinct challenges have been made.

I believe distinct or Group By could play a role, so I’ve tried:


SELECT count(*) AS countedTeamCompletedChallengesWithoutRepetition
FROM equipas e
INNER JOIN desafiosporequipa de ON de.cod_equipaFk = e.cod_equipa
INNER JOIN desafios d ON d.cod_desafio = de.cod_desafioFk
WHERE de.estado = 1 AND e.cod_equipa = ?
GROUP BY d.cod_desafio

But a get a:

countedTeamCompletedChallengesWithoutRepetition
1
1
1
1
1

instead of a number.

Can I have your help please? Again. :s

Thanks in advance,
Márcio

COUNT(DISTINCT d.cod_desafio)

and without the GROUP BY

:smiley: Thanks. Apparently, and from my first tests, it works.

Thanks for your time on analyzing it.

Update:
Really really thanks a lot.
Oh my oh my. Can’t wait to see it working on the script. :D:D:D

Cheers (again),
Márcio