CODEWITHSUNDEEP
sqlms-accesscountsubquery
Roël Gonzalez
Roël Gonzalez

Reputation: 72

SQL COUNT from othertable

Let's say I have two tables. One called Clients and One called Prizes

Clients and Prizes are related to eachother with a one to many relationship. For each time a client receives a prize it will be inserted in the Prizes table as an individual record.

How Can I determine with a query how many Prizes a certain client has received?

I want it to display as such: Clients.Clientname, [Number of prizes in Prizes table as integer number]

I have had a lot of struggle doing this.

I am trying to make this in Ms Access 2007

Thanks in advance guys.

Upvotes: 0

Views: 61

Answers (2)

Darth Continent
Darth Continent

Reputation: 2319

Assuming this is the table structure:

Clients

    ID
    ClientName


Prizes

    ID
    ClientID
    Description

You could do:

SELECT c.Clientname, COUNT(p.ID) AS NumberOfPrizes
FROM Clients c
LEFT JOIN Prizes p ON c.ID = p.ClientID
GROUP BY c.Clientname

Upvotes: 1

juergen d
juergen d

Reputation: 204884

select c.Clientname, 
       count(p.id) as prices_count
from clients c
left join prices p on p.client_id = c.id
group by c.Clientname

Upvotes: 0

Related Questions