CODEWITHSUNDEEP
mysqlsubquery
Ahmed
Ahmed

Reputation: 1533

MySQL subquery for group by fields

I have a MySQL table called sessions, this table has attendance of sessions attended by user_id with the date. I am able to get average attendance percentage by using the following syntax which basically divides total attended sessions by total possible sessions and multiplies it by 100. This gives me the entire average for the given date range.

SELECT (
(SELECT count(*) as total FROM sessions ap JOIN session_codes codes USING (code_id) WHERE ap.session_year=2017 AND (ap.session_date BETWEEN '2017-01-15' AND '2017-05-10') AND codes.status>=1) / 
(SELECT count(*) as total_sessions FROM sessions WHERE (session_date BETWEEN '2017-01-15' AND '2017-05-10') AND session_year=2017)
) * 100 AS percentage

However, what I would like to do is group the results by user_id and for each user_id run some sort of subquery to look like the above but also have a clause to say where user_id is the one from the loop. That way i can get the percentage for individual users and have the results look something like this:

user_id   attendance
1211      89
3344      96

does anyone know how this can be achieved?

I ran an explain syntax on the sessions table and this is how my sessions table looks:

user_id         bigint(20)   NO    PRI  NULL
session_year    year(4)      NO    PRI  NULL
session_date    date         NO    PRI  NULL
am_pm           bigint(20)   NO    PRI  NULL
code_id         bigint(20)   YES        NULL
user            varchar(50)  YES        NULL

Upvotes: 0

Views: 121

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use GROUP BY, e.g.:

SELECT user_id, count(*) as total 
FROM sessions ap JOIN session_codes codes USING (code_id) 
WHERE ap.session_year=2017 AND (ap.session_date BETWEEN '2017-01-15' AND '2017-05-10') AND codes.status>=1
GROUP BY user_id

This will give you attendance by user. You can now divide it by total to get the percentage, e.g.:

SELECT a.user_id, a.total / (SELECT count(*) as total_sessions FROM sessions WHERE (session_date BETWEEN '2017-01-15' AND '2017-05-10') AND session_year=2017 and user_id = a.user_id)
FROM (
   SELECT user_id, count(*) as total 
   FROM sessions ap JOIN session_codes codes USING (code_id) 
   WHERE ap.session_year=2017 AND (ap.session_date BETWEEN '2017-01-15' AND '2017-05-10') AND codes.status>=1
   GROUP BY user_id
) a;

Upvotes: 1

Related Questions