Counting while lisiting in a single SQL query

Question

The title is pathetic, I don't really know how to word the issue better than this, let me explain.

I've got a Tests table and TestAttendance table where I create a record each time user takes a test with user id and test id.

What I want to do is: While listing the tests I want to show total number of attendants for each test.

(
  SELECT COUNT(ta.id)
  FROM TestUserAttendances ta
  JOIN Tests t ON ta.testId = t.id
)

The above query returning the total number of attendants without distinguishing test, so the total number of attendants added to each test while I want it to display each test individually.

The quality of the title is commensurate with the quality of the question. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Jun 13 '20 at 19:28

score 1 · Accepted Answer · answered Jun 13 '20 at 19:28

1

You need to use GROUP BY:

SELECT ta.test_id,count(*) from FROM TestUserAttendances ta JOIN Tests t ON 
ta.testId = t.id group by ta.test_id

answered Jun 13 '20 at 19:28

XraySensei

167
7

1

Spamming the `accept as answer` button right now. Thanks, I did know about `group by` never thought about it. – Ertan Kara Jun 13 '20 at 19:31

Counting while lisiting in a single SQL query

1 Answers1