Tom Cruise Tom Cruise - 3 months ago 9
MySQL Question

Perform a complicated calculation in mysql query

I'm stuck trying to do calculations in sql query. I have table attendance which looks like this:

roll | sub_id | status
abc | 1 | 1
abc | 1 | 0
abc | 2 | 1
xcv | 1 | 1
abc | 2 | 1
abc | 1 | 1
lkj | 2 | 0


This is an example of my table subject:

id | name
1 | Data Structure
2 | Cloud Computing


I want to select distinct sub_id for particular roll and then count the number of status with 0 and status with 1 and link to the subject table and show their names.
and I want something like this :

roll | sub_id | name | status with 0 | status with 1
abc | 1 |Data Structure | 1 | 2
abc | 2 |Cloud Computing | 0 | 2


Can someone explain me ?
How can I approach with the query ?

Answer

You can do:

SELECT
  a.roll, 
  a.sub_id, 
  b.name, 
  SUM(Case when status=0 then 1 else 0 end) as 'status with 0',
  SUM(Case when status=1 then 1 else 0 end) as 'status with 1'
FROM
  myTable a inner join subject b on
  a.sub_id = b.id
  group by a.roll, a.sub_id;

I made a fiddle for you: http://sqlfiddle.com/#!9/23d1d9/11/0