Why isn't the first query producing correct result?

you don't have a group by for the second query, so I'd expect one result row for each matched item in VehicleModelYear.

Ask yourself this--

Do you want one result row for each kind of car, or do you want a result row for each matching vehicle?

If you want summarized data, then you run the first query. If you want other data, run the second query.

If you know exactly what question you are asking, try posting some sample data or put it in an sqlfiddle and we can help you compose an appropriate query.

ralmada,

Your result is just about the same as mine. Only difference is that *now*, it has acura(2) twice.

That is:
acura(2)
acura(2)

Mine has it like this:

acura(1)
acura2)

There are two acura legends for the year 1987.

What we would like to do indicate the number of available vehicle by MAKE for a given year. This way, when a new dropdown opens for models, user can then select a particular model for that MAKE.

mankowitz, using group by won't solve the problem; it doesn't seem to make any difference.

So what is the result you're expecting then? Please post sample data both input and output

In the mean time I'm just guessing:

select vehicleId, Year,
	( make+ '(' + CONVERT(NVARCHAR, count(*) over (partition by a.Make, a.Year) ) + ')') as Make 
from VehicleModelYear A 
order by a.Make

Select allOpen in new window

I have not gotten the model to load yet because I am still trying to resolve the MAKE problem.

However, please see the screenshot as the sample output we are looking for.

First, user selects year from the dropdownlist:

select DISTINCT Year from VehicleModelYear ORDER BY Year DESC

Select allOpen in new window

the dropdownlist control being passed to MAKE is ddlYear:

 cmd.Parameters.AddWithValue("@year", _
                             ddlYear.SelectedItem.Value)

Select allOpen in new window

This is the equivalence of declaring year and assigning it a value:

Declare @Year as integer

@Year="1987"

Then the code I posted; well just for the question, let me use your code:

select vehicleId,cast(year as varchar),model,
      ( make+ '(' + CONVERT(NVARCHAR, count(*) over (partition by Make) ) + ')') as Make
from VehicleModelYear A
where a.Year = @Year
order by a.Make

Based on this,  if the query is correct, selecting year will load all vehicles associated with 1987 and the available #s for each vehicle.

Then selecting a particular make, as stated will load the models associated with that make.

If I use DISTINCT with your code or mine, it works:

select distinct
	( make+ '(' + CONVERT(NVARCHAR, count(*) over (partition by Make) ) + ')') as Make 
from VehicleModelYear A 
where a.Year = 1987 

Select allOpen in new window

but I wonder if that's the best solution because it doesn't allow me to use the ORDER BY clause
ddlcars.JPG

I put some data into a fiddle: http://sqlfiddle.com/#!6/5fc7f/2

Tell me approximately what you are looking to achieve

I think this is what you're looking for:

select make, sum(case when year = @year then 1 else 0 end)  from vehiclemodelyear
group by make

The solution that I am not getting is how to put the count in brackets like:

Acura(2)

you have at least 2 cases (without year, with year)
do you want 'all' makes or just 'matched' makes?

without year    
  YEAR | Select Make
>>Year | Acura (2)
  2013 | Audi (1)
  2012 | BMW (1)
  2011 | Cadillac (2)
  2010 | Crysler (1)
       | Ford (0)       --<< if zero list it?
       | Skoda (1)
       | ...

with year - all       
  YEAR | Select Make
  Year | Acura (1)
>>2013 | Audi (1)
  2012 | BMW (0)       --<< if zero list it?
  2011 | Cadillac (0)
  2010 | Crysler (0)
       | ...

with year - matched       
  YEAR | Select Make
  Year | Acura (1)
>>2013 | Audi (1)
  2012 | Skoda (1)
  2011 | 
  2010 |  

Select allOpen in new window

Just all makes.

Users want to know what makes the dealer sells.

They just need to numbers in parenthesis to let the user know whether the make of the vehicle they are looking for is available or out of stock.

For instance,  Acura(2) tells them there are 2 vehicles of Acura in stock.
Audi(0) tells them this is out of stock.

User can contact them to know of availability.

There's a problem though. You're assuming you have all makes in the vehiclemodelyear table. You should have a separate table listing all makes... But anyhow, try this

select make + '('+ convert(varchar(30), sum(case when  (@year is null or year = @year) then 1 else 0 end)) + ')'  from vehiclemodelyear
group by make 

Select allOpen in new window

The above will give you all makes and all years when @year is null. if @year is not null it will still list all makes, but only the one in stock for that specific @year.

IF that's not what you want, can you post the out desired in a resulset format?

I absolutely agree:
You should have a separate table listing all makes

also:
I'm a user - and I only want to know what you have available e.g.

carsales.com.au 1920 - 1950

(no zero listed makes)