Avatar of sammySeltzer
sammySeltzerFlag for United States of America asked on

Why isn't the first query producing correct result?

Greetings mates,

Maybe someone alot more knowledgeable than me in SQL can explain this to me and there are certainly a lot of  you.

When I run this query:

select make, count(*) totalNumber from VehicleModelYear where make='acura' and year=1987
group by make

Open in new window

I get correct results.

For instance, I do have, for testing purposes, 2 acura legends for the year 1987 and it is listed correctly as:

Acura Legend(2)

If, however, I run this query:

select vehicleId,( make+ '(' + CONVERT(NVARCHAR, (select count(*) from VehicleModelYear B where a.vehicleId=b.vehicleId)) + ')') as Make from VehicleModelYear A where a.Year = 1987 order by a.Make

Open in new window

I get the following results:

Acura Legend(1)
Acura Legend(2)

Any reason for the descrepancies and how do I resolve this?

Just  a little history, I am building a car dealer web app.

Users will select Year from dropdownlist, say 1987.

Once this select is made, all vehicles and their Makes and models for the year 1987 are loaded into the second dropdown.

The functionality works but the results don't.

Your assistance is greatly appreciated.
ASP.NETMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question


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

That is:

Mine has it like this:


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

Open in new window

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

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

Open in new window

the dropdownlist control being passed to MAKE is ddlYear:

 cmd.Parameters.AddWithValue("@year", _

Open in new window

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

Declare @Year as integer


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 

Open in new window

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

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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


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 |  

Open 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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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 

Open 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

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)