CODEWITHSUNDEEP
mysqlsqlselect
D_R
D_R

Reputation: 4962

SQL Select from 2 tables and Group by month

Okay so I've got 2 table's

First one is called msg and the other one is msg_t

msg (id, send_type, ..) msg_t (id, msg_id, send_time)

What I am trying to do is to get all of the msg rows where send_type = 1

and to count the msg_t entries for each msg and group it by month

how can I do that?

Upvotes: 0

Views: 121

Answers (2)

John Woo
John Woo

Reputation: 263713

SELECT  a.ID, MONTHNAME(b.send_time), COUNT(b.msg_id) totalCount
FROM    msg a
        LEFT JOIN msg_t b
            ON a.ID = b.msg_id
WHERE   a.send_type = 1
GROUP   BY a.ID, MONTH(b.send_time)

by using LEFT JOIN, a value of zero will be displayed for msg.ID that have no records on table msg_t

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

SELECT
  m.id,
  MONTH(send_time)
  COUNT(t.*)
FROM msg m
INNER JOIN msg_t t ON m.id = t.msg_id
WHERE m.send_type = 1
GROUP BY m.id,
         MONTH(send_time)

Upvotes: 1

Related Questions