Call list + Attendees per event

Question

Call list + Attendees per event

Asked 6 years, 7 months ago

Viewed 110 times

0

I have the following tables:

I need to list COMPANIES AND ENROLLED IN THE EVENT (which may have more than one lecture) and in the same row bring the QTD OF GIFTS per lecture in columns... Thus, it would obtain a parameter of use per lecture specific to each participating company.

The presences in each lecture are recorded in the e_presenca via cod_incsricao. This code is validated by e_inscrito. If the subscriber is registered, the presence is included. ( saving the REGISTERED cod_inscricao / EVENT evento / LECTURE id_evento / COMPANY empresa)

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

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 number/id (I changed for example). On the table e_presença the tuple evento plays the role of the group and id_evento makes the sub-group (which ends up being a mirror of the table evento)

Please explain, in the question, the structure of your tables, as they do not seem to be normalized. For example, but don’t limit yourself to, why in the tables e_inscrito and e_presenca what appears is the company name and not the id? In e_presenca, what is the difference between evento and id_evento?

– Woss

2018/03/30 at 21:15
Okay, I’ll edit it. e_inscrito and e_presenca instead of ids, these have already been received this way. in the table e_presenca, event plays the role of the group and the sub-group id_event

– I. Filho

2018/03/30 at 21:47
@Itafilho first of all, I don’t understand why in the column empresa of the tables e_inscrito and e_presenca, is not bringing the id table e_empresas, I don’t think it’s just exhibition. Another thing, to help us help you, public on http:/sqlfiddle.com/ the structure so we can test the select you need, if not in the head, it takes more time and may even contain silly errors that end up passing.

– rbz

2018/03/31 at 01:18

1 answer

Browser other questions tagged php mysql join count

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

by aa_sp • **848** points · Answer 1 · 2018-04-02T17:09:02+00:00

I don’t really understand your structure, if you have how to normalize it would be much better...

I’ve put together an example (sql server) that you can test and evaluate if it helps you:

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

http://www.sqlfiddle.com/#! 18/27e79/1