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.
LVL 29
sammySeltzerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
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.
0
ralmadaCommented:
your WHERE clause is wrong, it should be like this:


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

from VehicleModelYear A 
where a.Year = 1987 
order by a.Make

Open in new window


althought you know you can get the same results by using partition

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerAuthor Commented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ralmadaCommented:
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

0
sammySeltzerAuthor Commented:
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", _
                             ddlYear.SelectedItem.Value)

Open 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 

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
ddlcars.JPG
0
mankowitzCommented:
I put some data into a fiddle: http://sqlfiddle.com/#!6/5fc7f/2

Tell me approximately what you are looking to achieve
0
ralmadaCommented:
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
0
sammySeltzerAuthor Commented:
The solution that I am not getting is how to put the count in brackets like:

Acura(2)
0
PortletPaulfreelancerCommented:
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

0
sammySeltzerAuthor Commented:
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.
0
ralmadaCommented:
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?
0
PortletPaulfreelancerCommented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.