Sum and Count in Sqlite

[newbie2]

This is my Presence table that tracks the students' attendence and absence



I can display the list of attendees on a grid easily
For this I use:

StrSql = "Select Fname,  Lname , " & _
" BirthDate, classe_name, subject from Student_tbl" & _
 " left join Presence_tbl on Student_tbl.ID = Presence_tbl .PID " & _
" left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
 " left join class_tbl on Presence_tbl.class_id = class_tbl.ID   " & _
  " Where Presence  = 1 and date_id = " & Lbl_Date(1).Caption
Set Rs = Cnn.OpenRecordset(StrSql)

However when I want to display the list of attendees with the number of sessions attended for each pupil, I can only get one record displayed on the grid.

StrSql = "Select Fname,  Lname , " & _
" BirthDate, classe_name, subject,  sum (presence) as presences from Student_tbl" & _
 " left join Presence_tbl on Student_tbl.ID = Presence_tbl .PID " & _
" left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
 " left join class_tbl on Presence_tbl .class_id = class_tbl.ID   " & _
  " Where Presence  = 1 and date_id = " & Lbl_Date(1).Caption & " GROUP BY Fname"
Set Rs = Cnn.OpenRecordset(StrSql)

I even used

count (presence) as presences

but doesn't seem to work

thank you

[Schmidt]

Since more than one table is involved via joins -
we cannot recommend anything else but:
- post a zip with your (reduced) sqlite-db
- or alternatively prepare a db-fiddle and post a link

Olaf

[newbie2]

Thank you sir
this is my mini database

[Zvoni]

The filter presence =1 is in the Where clause outside the left joins, rendering all left joins to inner joins.
put the filter in each on clause