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
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
Last Comment
earwig75
Where is the date and ID columns in your sample data?
ASKER
The date and ID are not relevant, I just have them there as samples of data in the tblCarRecords
ASKER
The query i am trying to come up with should output this:
CarType | Count
suv | 3
pickup | 3
compact | 1
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
ASKER CERTIFIED SOLUTION
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.
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
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