CODEWITHSUNDEEP
mysql
Tom Cruise
Tom Cruise

Reputation: 69

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 ?

Upvotes: 0

Views: 49

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You can use conditional aggregation in a pivot query to get the output you want. The subquery below calculates the tallies for status when the value is both 0 and 1, for each each roll/sub_id group.

SELECT t1.roll,
       t1.sub_id,
       COALESCE(t2.name, 'name is NA'),
       t1.`status with 0`,
       t1.`status with 1`
FROM
(
    SELECT roll,
           sub_id,
           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 attendance
    GROUP BY roll,
             sub_id
) t1
LEFT JOIN
subject t2
    ON t1.sub_id = t2.id

Follow the link below for a running demo:

SQLFiddle

Upvotes: 3

Dazak
Dazak

Reputation: 1033

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

Upvotes: 0

Related Questions