Specific SQL query to count

Question

I'm developing a CRM, that's why I created poles to separate my users.

I created a view which gathered all the information of a user.

I'm looking to know how to count the number of users logged in by pole.

In my VIEW I have a user_online field.

I also have a pole_name field.

I tried this request but it doesn't work.

SELECT pole_name, COUNT(user_online) AS nbr_online FROM `ViewProjet_userPoleRole` GROUP BY pole_name

Which gives me the total number of users of a pole and therefore not if it is online.

And finally, here is my entire VIEW.

I have tried several requests, but I cannot.

Maybe you can share the basic definition of both of your tables. Also whats the outcome of your query? — phaen, Feb 13 '20 at 09:20

score 1 · Accepted Answer · answered Feb 13 '20 at 09:24

1

I think you can use conditional aggregation as following:

SELECT
    POLE_NAME,
    SUM(CASE WHEN USER_ONLINE = 'Y' THEN 1 ELSE 0 END) AS NBR_ONLINE
FROM VIEWPROJET_USERPOLEROLE
GROUP BY POLE_NAME

or If you want to know only Poles with minimum one online user then put the condition in the WHERE clause as follows:

SELECT
    POLE_NAME,
    COUNT(1) AS NBR_ONLINE
FROM VIEWPROJET_USERPOLEROLE
WHERE USER_ONLINE = 'Y'
GROUP BY POLE_NAME

If you represent online user by some other kind of norms then use them in WHEN clause of the CASE statement accordingly.

Cheers!!

answered Feb 13 '20 at 09:24

Popeye

35,427
4
10
31

Thank you for your reply. Your request sends me back the poles but not the number of users online. I have the impression that it is reversed, that it rather shows me the number of offline users. – Benjamin Peuple Feb 13 '20 at 09:28
Disagree!!! It is counting the number of records where `USER_ONLINE = 'Y'` – Popeye Feb 13 '20 at 09:30
What does "Y" mean? I think we are on the right track with your request. – Benjamin Peuple Feb 13 '20 at 09:33
read this from my answer: **If you represent online user by some other kind of norms then use them in WHEN clause of the CASE statement accordingly.** – Popeye Feb 13 '20 at 09:38
1

I had not seen, thank you for clarifying me, I will remember this lesson ! – Benjamin Peuple Feb 13 '20 at 09:39

Nikhil Gyan · Answer 2 · 2020-02-14T12:03:50.437

1

Try this:

SELECT pole_name, COUNT(user_id) AS nbr_online 
FROM `ViewProjet_userPoleRole` 
WHERE user_online = 1
GROUP BY pole_name

Hope this will help

Or you can try the following if want to display both the online and offline count:

SELECT pole_name, SUM(if(user_online = 1,1,0)) as nbr_online, SUM(if(user_online = 0,1,0)) as nbr_offline 
FROM `ViewProjet_userPoleRole` 
GROUP BY pole_name

Try this.

edited Feb 14 '20 at 12:03

answered Feb 13 '20 at 09:26

Nikhil Gyan

682
9
16

Thank you for your reply. Your request works but only returns the poles with the connected users. I will need to display the poles with all of the users logged out as well. – Benjamin Peuple Feb 13 '20 at 09:34
1

SELECT pole_name, SUM(if(user_online = 1,1,0)) as nbr_online, SUM(if(user_online = 0,1,0)) as nbr_offline FROM `ViewProjet_userPoleRole` GROUP BY pole_name – Nikhil Gyan Feb 14 '20 at 12:04
Thank you very much for your help, it also works like that. – Benjamin Peuple Feb 14 '20 at 13:23
you my code is helpful for you, then please do the favor by upvoting my answers and more. – Nikhil Gyan Feb 15 '20 at 05:22
I just did it, thanks again. – Benjamin Peuple Feb 18 '20 at 12:34

Specific SQL query to count

2 Answers2