Select multiple counts, grouped by

[brut24]

Hi

I have two tables, Table1 and Table2 with fields like this:

Table1:
ID
Title
Level

Table 2:
ID
UserID
ListID (foreign key to table1.ID)
Status

The Status field can have one of two values, A or S. I am trying to select a count of A and a count of S, WHERE UserID is the logged in user (which is fine so far), but it needs to be grouped by Level from Table1

Level has possible values of Beginner, Middle and Advanced. So the results would look something like this:

Beginner(Level) 3(A) 0(S)
Middle (Level) 1(A) 1(S)
Advanced(Level) 0(A) 2(S)

I had this:

SELECT count(Table2.ID) AS Active FROM Table2 WHERE Table2.UserID = '19' AND Table2.Status = 'A', count(Table2.ID) AS Studied FROM Table2 WHERE Table2.UserID = '19' AND Table2.Status = 'S'

but am not sure how to include the GOUP BY Level clause?

[brut24]

Is anyone able to assist with this? I have tried a few options but am not making much progress.

Would really appreciate any input...

[topr8]

hmm, it's early in the morning but i think what you are tryoing to do and layit out like that is not trivial, ... don't you have to create a temporary table and populate it with the S level status and then the A level status

[enigma1]

why don't you use a join of the 2 tables with the ListID, then group by Level filter by UserID and select the status counts.