CODEWITHSUNDEEP
mysqlsql
Shima Mdz
Shima Mdz

Reputation: 195

MySQL Query (Group by)

2 I have two tables

one logs.emails:

EmailNum  EmployeeID,   Emailcontent,  EmailReceivers    ,is_read
  1          1       ,   "sasa"     ,   "[email protected]" ,1
  2          1       ,   "sasa"     ,   "[email protected]" ,0
  3          2       ,   "sasa"     ,   "[email protected]" ,0
  4          2       ,   "sasa"     ,   "[email protected]" ,0
  5          2       ,   "sasa"     ,   "[email protected]" ,0

and Employees.user

id, FirstName, LastNAme
1 , "John"   , "Brown"
2 , "Jack"   , "James"

My desired Output:

FirstName, LastName, NumOfUnreadEmails
John , Brown , 1
Jack , James ,3

My attempt(But it does not return the irst row of desired output which is "John , Brown ,1"):

SELECT 
    *, count(EmployeeID) as NumEmails 
FROM 
    logs.emails a
inner join 
    Employees.user b on a.EmployeeID=b.id 
group by 
    EmployeeID 
having 
    a.is_read='0'

Your help is appreciated

Upvotes: 0

Views: 90

Answers (3)

Mike Brant
Mike Brant

Reputation: 71384

You should specify a WHERE clause here instead of using HAVING, as you are trying to filter out the records that have is_read=0 prior to doing any aggregation.

Also, in order to get only the fields desired, don't use * . Simply specify the fields you want.

SELECT 
    b.FirstName, b.LastName, COUNT(a.EmailNum)
FROM 
    logs.emails a
inner join 
    Employees.user b on a.EmployeeID=b.id 
where 
    a.is_read='0'
group by 
    b.id

Upvotes: 3

GolezTrol
GolezTrol

Reputation: 116100

This will return all employees, including the ones that don't have any e-mail at all. If you want to show only the ones that do have unread e-mail, you can change LEFT JOIN to INNER JOIN.

SELECT 
  u.FirstName,
  u.LastName,
  COUNT(e.EmployeeID) AS NumOfUnreadEmails
FROM
  Employees.user u
  LEFT JOIN logs.emails e ON e.EmployeeID = u.id AND e.is_read = 0
GROUP BY
  u.id

Upvotes: 1

Robert
Robert

Reputation: 25753

Try this way:

SELECT b.FirstName, b.LastName, 
       sum( case
              when a.is_read=0 than 1
              else 0
             end ) as NumOfUnreadEmails
FROM logs.emails a 
inner join Employees.user b on a.EmployeeID=b.id 
group by b.FirstName, b.LastName

Upvotes: 1

Related Questions