Call list + Subscribers and participants per event

Question

Call list + Subscribers and participants per event

Navigation

#1 by (0 votes)

0

I have the following tables:

I need to list COMPANIES and REGISTERS IN THE EVENT (which can have more than one talk) and in the same line bring the QTD OF GIFTS by lecture in columns ... Thus I would get a parameter of use per lecture in specific for each participating company.

The attendances in each lecture are recorded in e_presenca via cod_incsricao . This code is validated by e_inscrito . If the enrollee is registered, presence is included. (saving% COW% / EVENT cod_inscricao / COURSE evento / COMPANY id_evento )

What I got so far was a non-dynamic table and unreal numbers with the following SQL, What am I doing wrong? Or what should you restructure in these (already populated) tables?

SELECT ins.empresa, COUNT(ins.empresa) Inscritos,
COUNT(CASE WHEN  pre.id_evento = '32' THEN 0 ELSE NULL END) AS UM,
COUNT(CASE WHEN  pre.id_evento = '25' THEN 0 ELSE NULL END) AS DOIS
FROM e_presenca pre
INNER JOIN e_inscrito ins ON ins.empresa = pre.empresa
WHERE ins.id_evento = '18' 
GROUP BY ins.empresa

The company names are actually in txt and not in numero / id (changed for example). In the table empresa the tuple e_presença plays the role of the group and evento does the sub-group (which ends up being a mirror of the id_evento table)

php mysql join count

asked by anonymous 30.03.2018 / 22:28

1 answer

Redirecting files to C files What is the reason for a runtime error in this code?

score 0 · Answer 1

I do not understand your structure much, if you have how normalize would be much better ...

I've set up an example (sql server) that you can test and evaluate if it helps:

CREATE TABLE EMPRESA
(
    ID_EMPRESA INT IDENTITY PRIMARY KEY,
    EMPRESA VARCHAR(255)
)

CREATE TABLE EVENTO
(
    ID_EVENTO INT IDENTITY PRIMARY KEY,
    CATEGORIA VARCHAR(255),
    TITULO VARCHAR(255),
    PALESTRANTE VARCHAR(255)
)

CREATE TABLE E_INSCRITO
(
    ID_INSCRITO INT IDENTITY PRIMARY KEY,
    ID_EMPRESA INT,
    ID_EVENTO INT,
    COD_INSCRICAO VARCHAR(50),
    PRESENCA BIT DEFAULT NULL
)

-- ### Empresa ### --
INSERT INTO EMPRESA (EMPRESA) VALUES ('Empresa 1')
INSERT INTO EMPRESA (EMPRESA) VALUES ('Empresa 2')
INSERT INTO EMPRESA (EMPRESA) VALUES ('Empresa 3')
INSERT INTO EMPRESA (EMPRESA) VALUES ('Empresa 4')
INSERT INTO EMPRESA (EMPRESA) VALUES ('Empresa 5')
--------------------

-- ### Evento ### --
INSERT INTO EVENTO (CATEGORIA, TITULO, PALESTRANTE) VALUES ('Evento 1', 'Palestra 1', 'Palestrante 1')
INSERT INTO EVENTO (CATEGORIA, TITULO, PALESTRANTE) VALUES ('Evento 2', 'Palestra 2', 'Palestrante 1')
INSERT INTO EVENTO (CATEGORIA, TITULO, PALESTRANTE) VALUES ('Evento 3', 'Palestra 3', 'Palestrante 1')
INSERT INTO EVENTO (CATEGORIA, TITULO, PALESTRANTE) VALUES ('Evento 4', 'Palestra 4', 'Palestrante 2')
INSERT INTO EVENTO (CATEGORIA, TITULO, PALESTRANTE) VALUES ('Evento 5', 'Palestra 5', 'Palestrante 2')
INSERT INTO EVENTO (CATEGORIA, TITULO, PALESTRANTE) VALUES ('Evento 6', 'Palestra 6', 'Palestrante 2')
--------------------

-- ### Inscritos ### --
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (1, 1, '100', 0)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (1, 1, '101', 0)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (1, 2, '102', NULL)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (1, 3, '103', 1)

INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (2, 1, '200', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (2, 1, '201', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (2, 1, '202', 0)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (2, 3, '203', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (2, 4, '204', NULL)

INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (3, 1, '300', 0)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (3, 4, '301', NULL)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (3, 6, '302', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (3, 6, '303', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (3, 6, '304', 1)

INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (4, 4, '400', NULL)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (4, 5, '401', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (4, 6, '402', 1)

INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (5, 5, '500', 1)
INSERT INTO E_INSCRITO (ID_EMPRESA, ID_EVENTO, COD_INSCRICAO, PRESENCA) VALUES (5, 5, '501', 1)
-----------------------



SELECT TITULO, EMPRESA, CASE WHEN PRESENCA = 1 THEN 'Confirmado' WHEN PRESENCA = 0 THEN 'Não confirmado' ELSE 'Não informado' END PRESENCA, COUNT(ISNULL(PRESENCA, 0)) TOTAL_PRESENCA
FROM E_INSCRITO
INNER JOIN EMPRESA ON EMPRESA.ID_EMPRESA = E_INSCRITO.ID_EMPRESA
INNER JOIN EVENTO ON EVENTO.ID_EVENTO = E_INSCRITO.ID_EVENTO
GROUP BY TITULO, EMPRESA, PRESENCA
ORDER BY TITULO, EMPRESA, PRESENCA

link