Help needed in Database Query

Question

0.00/5 (No votes)

See more:

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.

SQL

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

SQL

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.

SQL

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.

Posted 22-Jan-15 20:02pm

Muhammad Taqi Hassan Bukhari

Updated 22-Jan-15 20:51pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2015-01-22T20:08:00

Solution 1

There are certain rules when you accumulate data using query...
All data that presented by the query must be part of the GROUP BY clause or must be computed (like COUNT, MAX...)...
So if you want to add sent_on, you should do something like this:

SQL

SELECT message_id, COUNT(log_id), sent_on AS Users
FROM log_messages
WHERE from_name = 'abc.xyz@gmail.com'
GROUP BY message_id, sent_on;

Posted 22-Jan-15 20:08pm

Kornfeld Eliyahu Peter

Comments

Muhammad Taqi Hassan Bukhari 23-Jan-15 2:55am

I have updated the question, review it now.

Kornfeld Eliyahu Peter 23-Jan-15 3:03am

Your are saying that you want to display status, but do not create different row for each different status!
That's a logical contradiction! A single row can display a single status, but when you are creating a row using GROUP BY, each row represents a group of records with similar value in the columns you choose...
So if you make a group of messages of one user, what status do you expect to attach to it? Status of the first, last, Nth row? An average of status? What the average of status?

Muhammad Taqi Hassan Bukhari 23-Jan-15 4:05am

i want to show this on the web page, i think might be multiple views may help here, i want to group a message of one user and count total then count how many of total status is delivered, failed etc.

Vinay Raj K · Answer 2 · 2015-01-22T21:24:00

You can use case statements to achieve your requirement..
For example:

SQL

select count(sent_to) as TotalUsers,
count(case when log_status='Pending' then log_status end) as TotalPending,
count(case when log_status='Draft' then log_status end) as TotalDraft,
count(case when log_status='Delivered' then log_status end) as TotalDelivered,
count(case when log_status='Failed' then log_status end) as TotalFailed
from log_messages
where from_name = 'abc@gmail.com'
GROUP BY message_id,sent_to, sent_on,log_status;

use some thing like this and try..