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
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
earwig75

8/22/2022 - Mon
Jim Horn

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

ASKER
The date and ID are not relevant, I just have them there as samples of data in the tblCarRecords
earwig75

ASKER
The query i am trying to come up with should output this:

CarType  |  Count
suv          |  3
pickup    |  3
compact | 1
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Horn

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
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
earwig75

ASKER
@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