C
C
cajka-d2019-07-23 08:21:10
MySQL
cajka-d, 2019-07-23 08:21:10

How to count identical responses in MySQL tables for each institution?

There are three tables.
Table of institutions.

CREATE TABLE list_institutions (
  `id_institution` INT NOT NULL AUTO_INCREMENT,
  `uid_category` INT NOT NULL,
  `region` VARCHAR(100) NOT NULL,
    `area` VARCHAR(100) NOT NULL,
    `city` VARCHAR(100) DEFAULT '',
    `name_institution` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_institution`),
  FOREIGN KEY (uid_category) REFERENCES list_categories(id_category) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Table of fields/questions for the questionnaire
CREATE TABLE list_fieldes (
  `id_field` INT NOT NULL AUTO_INCREMENT,
    `type_field` VARCHAR(100) NOT NULL,
    `name_field` TEXT NOT NULL,
    `options_field` TEXT DEFAULT NULL,
    `required_field` INT DEFAULT NULL,
    `uid_form` INT NOT NULL,
  PRIMARY KEY (`id_field`),
  FOREIGN KEY (uid_form) REFERENCES list_form(id_form) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

And a table with the answers of institutions to the question of the questionnaire
CREATE TABLE list_answers (
  `id_answer` INT NOT NULL AUTO_INCREMENT,
    `uid_field` INT NOT NULL,
    `uid_questionnaire` INT NOT NULL,
  `uid_institution` INT NOT NULL,
    `answer_user` VARCHAR(300) NULL,
  PRIMARY KEY (`id_answer`),
  FOREIGN KEY (uid_field) REFERENCES list_fieldes(id_field) ON DELETE CASCADE,
  FOREIGN KEY (uid_questionnaire) REFERENCES list_questionnaires(id_questionnaire) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

The task is to find out for each institution the number of identical answers. The answers themselves are stored in the list_answers table, column answer_user, also in this table there is a uid_field column, which stores the number of the question to which the answer belongs.
Is there any way to do this in one request?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
D
Dmitry Shitskov, 2019-07-23
@Zarom

Is there any way to do this in one request?

Can

I
idShura, 2019-07-23
@idShura

SELECT   I.NAME_INSTITUTION,
         A.UID_FIELD,
         A.ANSWER_USER,
         COUNT (*) CNT_ANSWER
    FROM LIST_INSTITUTIONS I
         LEFT JOIN LIST_ANSWERS A ON A.UID_INSTITUTION = I.ID_INSTITUTION
GROUP BY I.NAME_INSTITUTION, A.UID_FIELD, A.ANSWER_USER
  HAVING COUNT (*) > 1

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question