CODEWITHSUNDEEP
phpmysql
Mohit Bumb
Mohit Bumb

Reputation: 2493

Returning Count of Users from Multiple Table With Same Query

I have 2 tables 1 is User table and 2 in Engage table

in User table I have UserID and other fields and in Engage table I've EntityType and UserID field, EntityType can be "Login" or any other what I want is I want result in one row which should returns count of totalusers, count of totalusers who have EntityType "Login" exists in Engage table and totaluser who have engage which any other EntityType other than "Login"

every member who's engage with anything have already entry of Login

What I tried is :

SELECT COUNT(U.UserID) as TotalUser, COUNT(E.UserID) as ActiveUser, COUNT(EU.UserID) as EngagedUser
FROM (`Users` U)
LEFT JOIN `Engagement` E ON `E`.`UserID`=`U`.`UserID`
LEFT JOIN `Engagement` EU ON `E`.`UserID`=`EU`.`UserID`
WHERE EU.EntityType!="Login" AND E.EntityType="Login"

But this query is returning too many rows

Upvotes: 1

Views: 54

Answers (1)

Ondřej Šotek
Ondřej Šotek

Reputation: 1812

You should use subselects for each user count you want to get - something like:

select (select count(*) from Users) as TotalUser, 
(select count(*) from Users join Engagement on Users.UserId=Engagement.UserId where EntityType='Login') as ActiveUser,
(select count(*) from Users join Engagement on Users.UserId=Engagement.UserId where EntityType!='Login') as EngagedUser

When one user can have more records in Engagement table:

select (select count(*) from Users) as TotalUser, 
(select count(DISTINCT Users.UserId) from Users,Engagement where Users.UserId=Engagement.UserId and EntityType='Login') as ActiveUser,
(select count(DISTINCT Users.UserId) from Users join Engagement on Users.UserId=Engagement.UserId where EntityType!='Login') as EngagedUser

Upvotes: 2

Related Questions