SQL Ambiguous column name 'Car_ID'.

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
Need help with why it keeps returning the above mentioned issue.

  --write a query that shows me how many times each car was rented in 2018.

 Select Distinct Count(Car_ID) CtCar, rental.car_id
  from [Car]
	left join [rental]  on car.car_id = Rental.Car_id
Where year(rental.start_date) = '2018'
group by rental.Car_ID

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
It's good coding stlye to prefix all columns with the table name or aliase from wich it comes. The error is raised cause you use a column without prefix can be found in (at least) two of your tables. Furhtermore your usage of DISTINCT is redundant when using GROUP BY at the same time.
Then when using a WHERE predicate on the LEFT JOINed table, then it is actually an INNER JOIN.
When not using COUNT(DISTINCT columnName), then you normally don't need to specify a column for COUNT().
And last but not least: Fiiter for dates not calculates values. Depeding on the used SQL server this prohibts index usage.

Thus its e.g.

SELECT  COUNT(*) AS CtCar,
        Car.Car_ID
FROM    Car
    INNER JOIN Rental ON Car.Car_ID = Rental.Car_ID
WHERE   Rental.start_date BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY Rental.Car_ID;

-- or
SELECT  COUNT(*) AS CtCar,
        C.Car_ID
FROM    Car C
    INNER JOIN Rental R ON Car.Car_ID = R.Car_ID
WHERE   R.start_date BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY C.Car_ID;

Open in new window

Assuming that the date columns are really DATE values.

p.s. the error was caused by the unprefixed column in the COUNT() function.
Karen SchaeferBI ANALYST

Author

Commented:
thanks for your assist, however, I am getting the following error:

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "Car.Car_ID" could not be bound.
ste5anSenior Developer

Commented:
hmm, is your database case-sensitive?

btw, another thing: use the schema prefix for the tables.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Karen SchaeferBI ANALYST

Author

Commented:
not sure how do I check that?

When I run your first option I get the following error msg.

Msg 8120, Level 16, State 1, Line 2
Column 'Car.car_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If you group by rental.Car_ID, you need to use that same prefix in the select.

So,

SELECT  COUNT(*) AS CtCar,
       rental.Car_ID
...
group by rental.Car_ID
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You don't need any join to get the counts:

SELECT car_id, COUNT(*) AS car_count
FROM [rental]
WHERE start_date >= '20180101' AND start_date < '20190101'
GROUP BY car_id

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial