query count distinct

[Bogdan Olteanu]

Hello, i'm trying to do a select and count the answers given by a user to a question where a user can answer to a question with multiple choices. This means that a question can have an multiple picks from a user.


With the following query i am able to count all the answers:

SELECT COUNT(t2.answer_manager_id), t3.value
FROM user_answer t1
LEFT JOIN user_answer_answer_manager t2 ON t2.user_answer_id = t1.id
LEFT JOIN answer_manager t3 ON t3.id = t2.answer_manager_id
GROUP BY t3.value


But it would return me for one of the answers a count with a value of 2 and i want to return me a value of 1



Here is a dump database for testing



-- --------------------------------------------------------
-- Host: 127.0.0.1
-- Server version: 5.7.15-0ubuntu0.16.04.1 - (Ubuntu)
-- Server OS: Linux
-- HeidiSQL Version: 9.3.0.5117
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Dumping database structure for survey
CREATE DATABASE IF NOT EXISTS `survey` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `survey`;

-- Dumping structure for table survey.answer_manager
CREATE TABLE IF NOT EXISTS `answer_manager` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`score` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table survey.answer_manager: ~3 rows (approximately)
DELETE FROM `answer_manager`;
/*!40000 ALTER TABLE `answer_manager` DISABLE KEYS */;
INSERT INTO `answer_manager` (`id`, `value`, `score`, `createdAt`, `updatedAt`) VALUES
(1, 'first answer', '5', '2016-10-19 20:41:09', '2016-10-19 20:41:09'),
(2, 'second answer', '10', '2016-10-19 20:41:09', '2016-10-19 20:41:09'),
(3, 'third answer', '15', '2016-10-19 20:41:09', '2016-10-19 20:41:09');
/*!40000 ALTER TABLE `answer_manager` ENABLE KEYS */;

-- Dumping structure for table survey.user_answer
CREATE TABLE IF NOT EXISTS `user_answer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`comment` longtext COLLATE utf8_unicode_ci,
`createdAt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table survey.user_answer: ~5 rows (approximately)
DELETE FROM `user_answer`;
/*!40000 ALTER TABLE `user_answer` DISABLE KEYS */;
INSERT INTO `user_answer` (`id`, `value`, `comment`, `createdAt`) VALUES
(1, '1', 'first answer', '2016-10-19 20:42:25'),
(2, '', 'first answer', '2016-10-19 20:42:40'),
(3, '', 'second answer', '2016-10-19 20:42:57'),
(4, '', 'first and third answer, '2016-10-19 20:43:13'),
(5, '', NULL, '2016-10-19 21:31:01');
/*!40000 ALTER TABLE `user_answer` ENABLE KEYS */;

-- Dumping structure for table survey.user_answer_answer_manager
CREATE TABLE IF NOT EXISTS `user_answer_answer_manager` (
`user_answer_id` int(11) NOT NULL,
`answer_manager_id` int(11) NOT NULL,
PRIMARY KEY (`user_answer_id`,`answer_manager_id`),
KEY `IDX_DE92F8D3AAD3C5E3` (`user_answer_id`),
KEY `IDX_DE92F8D317B3BC5D` (`answer_manager_id`),
CONSTRAINT `FK_DE92F8D317B3BC5D` FOREIGN KEY (`answer_manager_id`) REFERENCES `answer_manager` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_DE92F8D3AAD3C5E3` FOREIGN KEY (`user_answer_id`) REFERENCES `user_answer` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table survey.user_answer_answer_manager: ~7 rows (approximately)
DELETE FROM `user_answer_answer_manager`;
/*!40000 ALTER TABLE `user_answer_answer_manager` DISABLE KEYS */;
INSERT INTO `user_answer_answer_manager` (`user_answer_id`, `answer_manager_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(4, 3),
(5, 1),
(5, 2);
/*!40000 ALTER TABLE `user_answer_answer_manager` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;