• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

SQl Select Statement mergining two statement

Select LL.Name as LocationName, count(EL.CarID) as 'Total Cars' from Location LL
inner JOIN Car_Location EL on LL.ID = EL.LocationID
WHERE EL.IsActive = 1
GROUP BY LL.Name
ORDER by [Total Cars] desc

Select LL.Name, count(EL.CarID) as 'Total Cars', max(SS.Date) as MaxDate from Location LL
inner JOIN Car_Location EL on LL.ID = EL.LocationID
inner JOIN Car_Schedule SS ON SS.Car_LocationID = EL.ID
WHERE EL.IsActive = 1
GROUP BY LL.Name
ORDER by [Total Cars] desc

The first statement return following records

LocationName            Total Cars
Arizona                  66
New York            65
California            34

The Second statement wacks out my total car count which is correct from statement 1

How do I fix that

what I want is that I have Car schedule in another table which has a date field, I just want to get MAX Date field and get the result as follows using second select statement

LocationName            Total Cars      MaxScheDate
Arizona                  66            12/12/2011
New York            65            05/06/2012
California            34            04/03/2012


Please Help
0
Guru Ji
Asked:
Guru Ji
  • 5
  • 4
  • 2
  • +2
2 Solutions
 
ralmadaCommented:
try

Select 	LL.Name, 
	count(EL.CarID) as 'Total Cars', 
	max(SS.MaxDate) as MaxDate 
from Location LL
inner JOIN Car_Location EL on LL.ID = EL.LocationID
inner JOIN (select Car_LocationID, max(Date) as MaxDate from Car_Schedule group by Car_LocationID) SS ON SS.Car_LocationID = EL.ID
WHERE EL.IsActive = 1
GROUP BY LL.Name
ORDER by [Total Cars] desc

Open in new window

or

Select 	LL.Name, 
	count(EL.CarID) as 'Total Cars', 
	(select max(Date) from Car_schedule where Car_locationID = EL.ID) as MaxDate
from Location LL
inner JOIN Car_Location EL on LL.ID = EL.LocationID
WHERE EL.IsActive = 1
GROUP BY LL.Name
ORDER by [Total Cars] desc

Open in new window

0
 
jhheiderCommented:
You're doing a GROUP BY, so your COUNT() probably needs to be a SUM().
0
 
stalhwCommented:
Your problem is simple, and ralmada solutions would do the trick.

But I'm not sure if your EL.CarID field is a primary key in your Car_Location table, if it is (or if it is unique to each row), then you can simply change your COUNT(EL.CarID) to COUNT(DISTINCT EL.CarID) in your second query and it should give you the wanted results.
Select LL.Name, count(DISTINCT EL.CarID) as 'Total Cars', max(SS.Date) as MaxDate 
from Location LL
inner JOIN Car_Location EL on LL.ID = EL.LocationID
inner JOIN Car_Schedule SS ON SS.Car_LocationID = EL.ID
WHERE EL.IsActive = 1
GROUP BY LL.Name
ORDER by [Total Cars] desc

Open in new window


The issue is that when you join a new table, and that new table as multiple rows for each car_location, it means the final result will have many duplicates rows of car_location.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
David ToddSenior DBACommented:
Hi,

Possibly fixed by changing to this:

select
    LL.Name
    , count( distinct EL.CarID) as 'Total Cars'
...

HTH
  David
0
 
Guru JiAuthor Commented:
Thanks for everyone input but ralmada solution seems like worked for me,

I am working on it right now and will report any problem or will accept ralmada solution if everything worked as it is so far
0
 
stalhwCommented:
write2mohit
Like I said, ralmada solutions will work, but it very complex for nothing if your CarID is a unique field... then just doing a COUNT(DISTINCT EL.CardID) will be much simpler and it will be more efficient.
0
 
ralmadaCommented:
stalhw,

but it very complex for nothing
It is not,
also, I don't think count distinct will produce the same result.
0
 
stalhwCommented:
ralmada

Care to explain why Count(Distinct EL.CardID) wouldn't produce the same result if CarID is unique?
"Count(Distinct"  exists to do things just like that...

hopefully write2mohit will try it, and tell us...
0
 
David ToddSenior DBACommented:
Hi Chaps,

Without adequat test data we don't know - either solution could work. Ralmada's solution is most complete, but I do like the count distinct for its simplicity.

Asker needs to realize what joining the third table did to records prior to the action of group by clause.

Regards
  David
0
 
ralmadaCommented:
>>Care to explain why Count(Distinct EL.CardID) wouldn't produce the same result if CarID is unique?<<

Because of the multiple joins.
0
 
stalhwCommented:
If CarID is unique per row in the Car_Location table, it doesn't matter how many joins you do, count distinct, will count the number of DISTINCT values of CarID, so it will work...

FYI:
http://msdn.microsoft.com/en-us/library/ms175997.aspx
DISTINCT: Specifies that COUNT returns the number of unique nonnull values.
0
 
ralmadaCommented:
>> so it will work...<<

you have to realize that mine and yours are not the same suggestions and as such might not produce the same results. But anyhow, let's just agree to disagree. I think dtodd just told you the answer: "Without adequat test data we don't know ".
0
 
stalhwCommented:
I can garantee you that IF CarID is unique per row in the Car_Location table, then you queries and mine will produce the same results.
The difference is that your 2 solutions are using subqueries, and that means they will be a bit less efficient, may not matter on tables with a few hundred records, but would matter if you're dealing with millions of records...

But if CarID is not unique (I doubt it, but we don't know), your 2 solutions are the way to go.

PS: And I think your first solution would be the most efficient of the 2, since it will have to query with grouping on Car_Schedule only once.
0
 
Guru JiAuthor Commented:
Hi stalhw,

Thanks for all your input and your comments in regards to using Count(Distinct EL.CardID) did worked and it works also with the ralmada solution posted at very first post.

The only thing is I cannot see any major different if I use Distinct in the count or without it as I am not dealing with millions of records but certainly it will make a difference if I use Distinct. Again I Thank you for all your input but I will still choose ralmada and would still give you a bit credit for making a valid input if I were to have millions of records.

Thanks everyone for the input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now