?
Solved

SQl Select Statement mergining two statement

Posted on 2012-09-11
14
Medium Priority
?
534 Views
Last Modified: 2012-09-17
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
Comment
Question by:Guru Ji
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 1800 total points
ID: 38388025
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
 
LVL 3

Expert Comment

by:jhheider
ID: 38388053
You're doing a GROUP BY, so your COUNT() probably needs to be a SUM().
0
 
LVL 8

Assisted Solution

by:stalhw
stalhw earned 200 total points
ID: 38388213
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 35

Expert Comment

by:David Todd
ID: 38388611
Hi,

Possibly fixed by changing to this:

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

HTH
  David
0
 
LVL 11

Author Comment

by:Guru Ji
ID: 38392053
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38392090
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38392113
stalhw,

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

Expert Comment

by:stalhw
ID: 38392134
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38392187
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38392206
>>Care to explain why Count(Distinct EL.CardID) wouldn't produce the same result if CarID is unique?<<

Because of the multiple joins.
0
 
LVL 8

Expert Comment

by:stalhw
ID: 38392250
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38392370
>> 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
 
LVL 8

Expert Comment

by:stalhw
ID: 38392821
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
 
LVL 11

Author Comment

by:Guru Ji
ID: 38406006
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question