Count records - two tables?

Databases
1

Hi

I sincerely hope someone can help me out.

I have two tables, “TableA” & “TableB”.

“TableA” has two fields “customername” & “manufacturer”. The data is the table looks something like this:

customername | manufacturer
Ray Smart | mercedes benz
Kim Dart |bmw
Stuart Keys | porsche
Matt Tumbridge | audi

My second table (TableB) has two fields. “manufacturer” & “model”

manufacturer | model
porsche | 911
porsche | cayman
bmw | 3 series
audi | a4
bmw | 5 series
porsche | Boxster
mercedes benz | c class
mercedes benz | slk
porsche | cayenne
bmw | z4

Basically, I need to find out how many models (COUNT) a customer may be interested in. So, for example for Stuart Keys the result should be 4 because there are 4 records in “TableB” associated with the manufacturer Porsche. For Kim Dart the results would be 3.

I’m a novice when it comes to SQL statements but I have a feeling I need to use a JOIN (I could be wrong).

I have tried to figure this out for days but I’m not having any luck therefore any help would be fully appreciated.

Best regards

Rod from the UK

2

You are right :smile:
Use an INNER JOIN, a GROUP BY and a COUNT. If you can’t get it to work, post the query you’ve got so far and we’ll be able to point you in the right direction :wink:

3

Yes, you need to JOIN the two tables. You’ll also need to use COUNT() AND GROUP BY

A sample which might get you where you started

SELECT t1.FieldName, COUNT(t2.fieldname)
  FROM table1 t1
  JOIN table2 t2 ON t1.commonField = t2.commonField
 GROUP BY t1.fieldName

[edit]

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.