Link to home
Start Free TrialLog in
Avatar of earwig75
earwig75

asked on

Requesting help with creating an SQL query with 2 tables

I have 2 tables, the first table (tblCarTypes) has 2 columns, CarTypeID and CarType. The 2nd table (tblCarRecords) has some records, where one of the columns is the CarType (which is populated with the CarTypeID). I want to create a query that will output a count of records from carRecords for each CarType. Samples of the data for each table is below.

The output I would like based on the sample data below would be like this below example. Could someone assist?

This is what I want the query to output:
CarType  |  Count
suv          |  3
pickup    |  3
compact | 1


tblCarTypes:
CarType    |   CarTypeID
suv            |         1
pickup       |         2
compact    |         3


tblCarRecords:
Date     |  CarType  |  ID
3/2/15   |    1           |   1
3/3/15   |    1           |    2
3/4/15   |    1           |    3
3/4/15   |    2           |    4
3/4/15   |    2           |    5
3/4/15   |    2           |    6
3/4/15   |    3           |    7
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Where is the date and ID columns in your sample data?
Avatar of earwig75
earwig75

ASKER

The date and ID are not relevant, I just have them there as samples of data in the tblCarRecords
The query i am trying to come up with should output this:

CarType  |  Count
suv          |  3
pickup    |  3
compact | 1
Ok.  I misread that as a source.  Give this a whirl..
SELECT CarType, [Count]
FROM (
	SELECT ct.CarType, ct.CarTypeID, COUNT(cr.CarType) AS [Count]
	FROM tblCarTypes ct
	   JOIN tblCarRecords cr ON ct.CarType = cr.CarType
	GROUP BY ct.CarType, ct.CarTypeID) a
ORDER BY CarTypeID

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Jim, i was getting errors when it tried to convert the results to int.

I ended up just changing a couple of the columns @scott used. Below is what works. Thank you.

SELECT ct.CarType, cr.[Count]
FROM (
    SELECT CarType, COUNT(*) AS [Count]
    FROM tblCarRecords
    GROUP BY CarType
) AS cr
INNER JOIN tblCarTypes ct ON ct.CarTypeID = cr.CarType