MySQL database | querying count() and select at the same time

Question

i am using MySql workbench 5.7 to run this.

i am trying to get the result of this query:

SELECT COUNT(Users) FROM UserList.custumers;

and this query:

SELECT Users FROM UserList.custumers;

at the same table, meaning i want a list of users in one column and the amount of total users in the other column.

when i tries this:

SELECT Users , COUNT(Users) FROM UserList.custumers;

i get a single row with the right count but only the first user in my list....

Answer 1

You can either use a cross join since you know the count query will result in one row... whose value you want repeated on every row.

SELECt users, userCount
FROM  userlist.custumers
CROSS JOIN  (Select count(*) UserCount from userlist.custumers)

Or you can run a count in the select.... I prefer the first as the count only has to be done once.

SELECT users, (SELECT count(*) cnt FROM userlist.custumers) as userCount 
FROM userlist.custumers

Or in a environment supporting window functions (not mySQL) you could count(*) over (partition by 1) as userCount

The reason you're getting one row is due to mySQL's extension of the GROUP BY which will pick a single value from non-aggregated columns to display when you use aggregation without a group by clause. If you add a group by to your select, you will not get the count of all users. Thus the need for the inline select or the cross join.

Consider: -- 1 record not all users

SELECT Users , COUNT(Users) FROM UserList.custumers;  

vs --all users wrong count

SELECT Users , COUNT(Users) FROM UserList.custumers group by users;

vs -- what I believe you're after

SELECT Users, x.usercount FROM UserList.custumers 
CROSS JOIN  (Select count(*) UserCount from userlist.custumers) x