user6599525 user6599525 - 1 month ago 8
SQL Question

using where in count sql

I am having following sql , it works fine

SELECT Count(*) as total,e.FirstName,e.LastName
FROM [Messages] as m
inner join Employees as e on m.SenderId=e.UserId
group by e.EmployeeId, e.FirstName,e.LastName


but i want to also have total unread message as column

how can i achieve this in sql ?

SELECT Count(*) as total,Count(where m.isRead=false) as totalUnreadMessage e.FirstName,e.LastName
FROM [Messages] as m
inner join Employees as e on m.SenderId=e.UserId
group by e.EmployeeId, e.FirstName,e.LastName

Answer

You are almost there, use a case expression to do conditional counting:

SELECT Count(*) as total,Count(case when m.isRead = false then 1 end) as totalUnreadMessage,
    e.FirstName,e.LastName
FROM [Messages] as m 
inner join Employees as e on m.SenderId=e.UserId
group by e.EmployeeId, e.FirstName,e.LastName
Comments