Count Distinct from Differnet Table

[JazzBass]

I'm trying to get a distinct count (example cars) that are stored in another table. I know in Access you need something like:

Select count(cartype) from (Select distinct(cartype) from DailyData)

But, I have the following SQL pieced together and all I can get is count (includes duplicates)

SELECT a.wDate, Sum(a.Sales) AS TotalSales, Count(b.cartype) as DiffCars 
FROM DailyData a left join AllCarTypes b on a.wDate = b.wDate
where a.wDate = #06-27-2001#
GROUP BY a.wDate

I don't know the syntax to get the distinct count in my query.

Anyone have any ideas?

[BruceG]

Hi Jazz,
Sorry I didn't get a chance to pick up on this yesterday. I'm at a loss for what you are trying to accomplish here (maybe I haven't had enough coffee yet) - can you describe what you want to achieve with the query?

[JazzBass]

Hi Bruce!

Not a problem. I appreciate the response!

I hope this helps.

DailyData

wDate		Sales
06/27/2002	1,000
06/28/2002	500

AllCarTypes

wDate		CarType
06/27/2001	Mustang
06/27/2001	Mustang
06/27/2001	Focus

Query:

SELECT Format(a.wDate, "mm yyyy"),
Sum(a.Sales) AS TotalSales,
Count(b.cartype) as DiffCars
FROM DailyData
a left join AllCarTypes b on a.wDate = b.wDate
where
Format(a.wDate, "mm yyyy") = '06 2001'
GROUP BY Format(a.wDate, "mm yyyy")

I guess what I want is my results to look like this:

Date		Sum(Sales)	DiffCars
06 2001		1,500		2

Hopefully that makes sense.

Thanks,
JazzBass

[BruceG]

Challenging problem, working on it ...

[JazzBass]

Originally posted by BruceG
Challenging problem, working on it ...

Oh boy! I didn't think it would be. My guess is that it's Access, isn't it?

In theory, Oracle and Sql Server should be able to use Count(Distinct(CarType)) and be ok.

Don't spend to much time on it. If needbe, I'll make another pass at the database and retrieve the results. I was just hoping to minimize trips to and back from the database.

Thanks for the time and effort.

JazzBass

[BruceG]

No problem ... here's what I've come up with. I tested in Access with your sample data and it works.

SELECT a.SalesDate, a.SalesSum, c.DistCarCount
FROM [select format(date, "mm yyyy") as SalesDate, sum(sales) as SalesSum from DailyData group by format(date, "mm yyyy")]. AS a INNER JOIN [select b.CarDate, count(*) as DistCarCount from (select distinct format(date, "mm yyyy") as CarDate, CarType from AllCarTypes) as b group by b.CarDate]. AS c ON a.SalesDate = c.CarDate
WHERE a.SalesDate = "06 2001";

[JazzBass]

Bruce,

Looks like that did it!!!!

Can you please tell me how I can learn some of these concepts without tearing my hair out?

I appreciate all the help you've given me!!

JazzBass

[JazzBass]

Bruce,

Remember my other tread about the Goals and such? Do you think I could implement that together with what you've given me, so far?

I'm heading home in a bit, and I'll try something there.

Take care and thanks ever so much.

JazzBass

[BruceG]

No problem! The best I can give is, like anything else, break down a complex problem into simpler pieces. In this case, I had to figure out what two recordsets needed to be joined to produce the desired results - this resulted in the two "Select" table expressions that appear on either side of the join. Then the main SELECT could then draw from the columns generated by this join. Just keep at it and keep yourself involved in data projects and over time solutions will come to you easier - believe me, I've torn my hair out many times ...

[JazzBass]

Thanks for the tips.

I guess I just haven't used Joins like the Inner, Left and such and I wasn't very sure on the context.

Also, I'm still very new to using multiple queries to get several different recordsets.

I hope I'll get the Goals part done at home and I'll post back tomorrow.

Thanks again,
JazzBass