1

I have a table as below.

sample

Is it possible that I can make the output as above?

Thanks

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
Taylern
  • 116
  • 1
  • 13
  • It looks like you want the latest date for each id. But what msg do you want for each id? I'm assuming that in reality each msg won't simply be "msg..."? – grin0048 Sep 14 '14 at 05:10
  • msg... means just message like hi blabla... – Taylern Sep 14 '14 at 05:44
  • do you have primary key in the table? – Jatinder Kumar Sep 14 '14 at 05:44
  • we don't want, what the message could be. We want to know if you want to show latest message, as you want the latest date to be displayed. Or do you mean message will be same in all records from smith/linda – Jatinder Kumar Sep 14 '14 at 05:45
  • Yes. there is a primary key as msg_id(integer) – Taylern Sep 14 '14 at 05:47
  • Ok, so when there are multiple records for a given id, which record's msg do you want to show? It's not clear from your example since all your msg values are the same. – grin0048 Sep 14 '14 at 05:48
  • possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](http://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) – Oliver Sep 15 '14 at 19:04
  • SELECT tbl_msg.msg_id, tbl_msg.msg_sender, tbl_msg.msg_receiver, tbl_msg.msg_content, tbl_msg.msg_date, tbl_users.user_fname, tbl_users.user_lname FROM tbl_msg LEFT JOIN tbl_users ON tbl_msg.msg_sender = tbl_users.user_no WHERE (tbl_msg.msg_receiver = 'staff') GROUP BY tbl_msg.msg_sender ORDER BY tbl_msg.msg_date DESC – Taylern Sep 16 '14 at 08:14

5 Answers5

0

Here is the MYSQL Query to do that

SELECT id, date, msg, COUNT(id) AS count FROM table_name GROUP BY id

Balavishnu R
  • 43
  • 5
  • My real query is SELECT tbl_msg.msg_id, tbl_msg.msg_sender, tbl_msg.msg_receiver, tbl_msg.msg_content, tbl_msg.msg_date, tbl_users.user_fname, tbl_users.user_lname FROM tbl_msg LEFT JOIN tbl_users ON tbl_msg.msg_sender = tbl_users.user_no WHERE (tbl_msg.msg_receiver = 'staff') GROUP BY tbl_msg.msg_sender ORDER BY tbl_msg.msg_date DESC – Taylern Sep 14 '14 at 05:38
  • But error Column tbl_msg.msg_id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Taylern Sep 14 '14 at 05:39
0

Try following query:

SELECT t1.id, t1.date, t1.msg, t2.msg_count 
FROM table_name t1 
JOIN (SELECT max(msg_id) as mid, count(*) as msg_count FROM table_name GROUP BY id) as t2 
ON(t1.msg_id=t2.mid)
Jatinder Kumar
  • 503
  • 6
  • 17
0

Seems like you want latest date in the result set, so the query would look like something, SELECT id, max(date), msg, COUNT(id) AS count FROM table_name GROUP BY id

sidd
  • 29
  • 1
  • 4
0

For sql server, try this :

SELECT id, max(date) as date, msg, count(id) AS count 
FROM table_name 
GROUP BY id, msg
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
0
SELECT T.id, Q.max_date, count(T.msg)
FROM Table1 T, (select id, MAX(dateD) as max_date from Table1 group by id) Q
where Q.id=T.id
group by T.id, Q.max_date
;
Nhung Phan
  • 41
  • 1
  • 5
  • 2
    Please consider adding some description to your answer, just pasting a code snippet might solve the immediate problem at hand but not add any understanding or long term value. – Capricorn Sep 13 '18 at 15:21