MySQL Query (Group by)

Question

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

Should Jack James have id of 2 above? They both have same id presently. — Hart CO
– Hart CO, Commented Jul 23, 2013 at 17:53
You can't do *, COUNT( since you would have to group all columns. Are you just wanting * from Employees.user? — David Starkey
– David Starkey, Commented Jul 23, 2013 at 17:54

Mike Brant · Accepted Answer · 2013-07-23 18:30:20Z

3

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

edited Jul 23, 2013 at 18:30

answered Jul 23, 2013 at 17:57

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GolezTrol Over a year ago

This doesn't return employees that have no unread e-mails or no e-mails at all. Maybe that's okay, but it's something to be aware of.

Shima Mdz Over a year ago

I dont want to return either!

Mike Brant Over a year ago

That was not mentioned as a requirement, but if needed, you could refer to the answer that you had provided which provides this result.

GolezTrol Over a year ago

I'm not saying either is better. Just pointing out the differences. :)

Shima Mdz Over a year ago

@Mike: no my query is wrong count(EmployeeID) needs to be replaced with COUNT(a.EmailNum)

|

Robert · Accepted Answer · 2013-07-23 18:00:55Z

1

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

edited Jul 23, 2013 at 18:00

answered Jul 23, 2013 at 17:53

Robert

25.8k8 gold badges70 silver badges86 bronze badges

1 Comment

spencer7593 Over a year ago

This will not return a count of zero for any employees.user that doesn't have any email. There are ways to write the statement that will return a zero count, as well as (potentially) improve performance by including a predicate on logs.emails.is_read = 0

GolezTrol · Accepted Answer · 2013-07-23 18:02:35Z

1

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

edited Jul 23, 2013 at 18:02

answered Jul 23, 2013 at 17:57

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

3 Comments

spencer7593 Over a year ago

This is SO close! But aggregate really needs to be something like COUNT(e.EmployeeID), so that we get a zero count for the users with zero unread emails.

spencer7593 Over a year ago

+1. Yes, that's the ticket. We might note that a "covering index" e.g. ON logs.emails (EmployeeID, is_read) may significantly improve performance of the query. (And now just simply removing the LEFT keyword would eliminate the rows with a zero count.)

spencer7593 Over a year ago

We might also mention that other DBMS would require "GROUP BY u.FirstName, u.LastName" (i.e. all non-aggregates in the SELECT list). But the query above is valid with MySQL, but that's a because of a MySQL specific extension which is not SQL-92 compliant.

Collectives™ on Stack Overflow

MySQL Query (Group by)

3 Answers 3

6 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related