CODEWITHSUNDEEP
mysqlsqlsqlite
mesibo
mesibo

Reputation: 4348

select the last record in each group along with count

We are having a following sqlite3 table named 'atable'

id  student assignment      grade
-----------------------------------
1    A       123             9
2    A       456             9
3    A       234             8
4    B       534             7
5    B       654             9
6    C       322             7

id is unique and incremented for each records. We are fetching latest assignment for each user by running query

SELECT student, assignment, grade from atable where id in 
       (select max(id) from atable group by student) order by id desc

This is working fine. However, we also need to fetch number of assignments for each user where user received a particular grade in the same query, say 9.

Any idea suggestion how to enhance or rewrite above query to return count as well. As mentioned, we are using sqlite3.

Thanks

Upvotes: 1

Views: 985

Answers (3)

sagi
sagi

Reputation: 40491

You can use this correlated query:

SELECT t.student, t.assignment, t.grade, 
       (SELECT COUNT(*) FROM atable s
        WHERE s.student = t.student and s.grade >= 9) as total_above_9
from atable t
where t.id in 
   (select max(id) from atable group by student)
order by t.id desc

Upvotes: 2

Blank
Blank

Reputation: 12378

Try this;)

select t1.student, t1.assignment, t1.grade, t2.count
from atable t1
inner join (select max(id) as id, count(if(grade=9, 1, null)) as count from atable group by student) t2
on t1.id = t2.id
order by t1.id desc

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

It would be better to join to a derived table that contains an aggregated version of the original table:

select t1.student, t1.assignment, t1.grade, t2.cnt 
from mytable as t1
join (
   select student, max(id) as id, 
          count(case when grade = 9 then 1 end) as cnt
   from mytable 
   group by student
) as t2 on t1.id = t2.id

Upvotes: 0

Related Questions