I want to display a data table with a query using many selects and various conditions, I've tried it but the result is that each column content keeps repeating and many duplicates.
I hope someone can help solve my problem
the result of my query :
While the result I want is as follows :
| agent | blast | replied | responded | awaiting | no_reply |
|---|---|---|---|---|---|
| Harry | |||||
| Andrian | 50 | 20 | 10 | 10 | 5 |
| James | 40 | 20 | 15 | 6 | 8 |
| Superadmin | 100 | 20 | 10 | 10 | 50 |
I tried :
SELECT t1.agent,
t2.blast,
t3.replied,
t4.responded,
t5.awaiting,
t6.no_reply
FROM (SELECT realname AS agent
FROM user) AS t1 -- this is for agent,
(SELECT count(*) AS blast
FROM message_thread
right join user
on message_thread.agent_id = user.id
right join message
on message_thread.id = message.message_thread_id
where message.status = 'sent'
group by message_thread.agent_id) AS t2 -- this is for blast,
(select count(*) as replied
from message_thread
right join user
on message_thread.agent_id = user.id
right join message
on message_thread.id = message.message_thread_id
where message.status = 'delivered'
group by message_thread.agent_id) AS t3 -- this is for replied,
(SELECT count(DISTINCT a.id) AS responded
FROM message_thread a
right join user
on a.agent_id = user.id
WHERE
EXISTS(
SELECT 1 FROM message_thread b
right join message e
on b.id = e.message_thread_id
WHERE a.id = b.id
AND e.status = 'sent'
) and exists (
SELECT 1 FROM message_thread c
right join message f
on c.id = f.message_thread_id
WHERE a.id = c.id
AND f.status = ''
)and exists(
SELECT 1 FROM message_thread d
right join message g
on d.id = g.message_thread_id
WHERE a.id = d.id
AND g.status = 'delivered'
)
group by a.agent_id) AS t4 -- this is for responded,
(SELECT count(DISTINCT a.id) AS awaiting
FROM message_thread a
right join user
on a.agent_id = user.id
WHERE
EXISTS(
SELECT 1 FROM message_thread b
right join message e
on b.id = e.message_thread_id
WHERE a.id = b.id
AND e.status = 'sent'
)and not exists (
SELECT 1 FROM message_thread c
right join message f
on c.id = f.message_thread_id
WHERE a.id = c.id
AND f.status = ''
)and exists(
SELECT 1 FROM message_thread d
right join message g
on d.id = g.message_thread_id
WHERE a.id = d.id
AND g.status = 'delivered'
)
group by a.agent_id) AS t5 -- this is for awaiting,
(SELECT count(DISTINCT a.id) AS no_reply
FROM message_thread a
right join user
on a.agent_id = user.id
WHERE not exists(
SELECT 1 FROM message_thread d
right join message g
on d.id = g.message_thread_id
WHERE a.id = d.id
AND g.status = 'delivered'
)
group by a.agent_id) AS t6 -- this is for no_reply
