i have following tables in my db, relation is one to many. a message is sent to multiple users. and want to get a list summary of messages sent by a user.
tbl_message(message_id, subject, content, category)
tbl_log_message(log_id, msg_id, sent_by, sent_on, status)
i want to fetch message subject, and total numbers of messages, sent_on by a particular user with message status.
like this
msg_subjectwas sent to 50 users. Pending = 20, Draft = 10, Delivered = 15, failed = 5
log_id ... msg_id ... sent_to ...... sent_by ... sent_on ... status
1............5......abc1@gmail.com...admin@gmail.com... 15-10-2014... Draft
2............5......abc2@gmail.com...admin@gmail.com... 15-10-2014... Draft
3............5......abc3@gmail.com...admin@gmail.com... 15-10-2014... Pending
4............5......abc4@gmail.com...admin@gmail.com... 15-10-2014... Delivered
5............7......abc5@gmail.com...admin@gmail.com... 15-12-2014... Delivered
6............7......abc6@gmail.com...admin@gmail.com... 15-12-2014... Delivered
7............7......abc7@gmail.com...admin@gmail.com... 15-12-2014... Pending
8............7......abc8@gmail.com...admin@gmail.com... 15-12-2014... Pending
9............7......abc9@gmail.com...admin@gmail.com... 15-12-2014... Draft
10...........7.....abc10@gmail.com...admin@gmail.com... 15-12-2014... Failed
I have write this query so far.
SELECT msg_id, COUNT(log_id), sent_on AS Sent_ON, log_status
FROM log_messages
WHERE from_name = 'admin@gmail.com'
GROUP BY msg_id, sent_on, log_status;
but it returns separate rows for pending, delivered, messages.