SUM two user roles in the same COLUMN

user_15892864 0
Viewed 53

How do I SUM all users, I have two different users. One is student and the second is instructor.

SELECT
(SELECT COUNT(user_role) FROM tbl_users WHERE user_role = 'student') as student, 
(SELECT COUNT(user_role) FROM tbl_users WHERE user_role = 'instructor') as instructor,
(SELECT SUM(user_role) FROM tbl_users) as totaluser

and why it doesn't add result as 1 in totaluser? It's 1 + 0 = 1. There should be at least 1 total user(s).

enter image description here

mysql
4 Answers

This is what you need

select student, instructor, student+instructor totaluser FROM ( 
select 
sum( if( user_role = 'student', 1, 0 ) ) AS student, 
sum( if( user_role = 'instructor', 1, 0 ) ) AS instructor  
from tbl_users ) t_alias
user_57376540

2 options ; 1 - you can use "union all" for sum of the different selects

SELECT name, COUNT(name) AS count
FROM table
GROUP BY name

UNION ALL

SELECT 'SUM' name, COUNT(name)
FROM table

or 2-

SELECT 
(select count(*) from table1 where selectedID = '123')
+
(select count(*) from table2 where selectedID = '123')
+
(select count(*) from table3 where selectedID = '123')
user_152528950 
SELECT student, instructor, (SUM(student)+SUM(instructor)) AS totaluser
FROM tbl_users
user_37895110

You can write it in this way: '''select student,instructor,(student+instructor) as sum from(SELECT (SELECT COUNT(user_role) FROM tbl_users WHERE user_role = 'student') as student, (SELECT COUNT(user_role) FROM tbl_users WHERE user_role = 'instructor') ) table'''

user_157816800
Related