asked on

max of count disaster MS SQL Server

oMG aggregate nightmare ( but there are worse nightmares {:P )

Goal:
Find the name and license of the customer who has Test_Driven the maximum number of cars.

Bad attempt:
      Select c.cname, c.license from Customer c where c.license in
       (Select t.license, count(distinct t.license) as count_drives from Test_Drives t
where count_drives = max(count_drives) group by t.license );

(see attached jpegs for better attempt)
Customer.jpg
Test-Drives.jpg
innerMAXofCount.jpg
outerMAXofCount.jpg

Kevin Cross

Hello kayvey,

Try this (see code snippet).

Regards,

mwvisa1

Select TOP 1 c.cname, c.license, td.count_drives
from Customer c 
inner join (select license, count(distinct t.license) as count_drives from Test_Drives t group by t.license) td
on td.license = c.license
group by c.name, c.license
order by td.count_drives DESC

Open in new window

Kevin Cross

And your original count based on distinct license will probably not work in my code as this will end up being 1 for all drivers, so try this:

Select TOP 1 c.cname, c.license, td.count_drives
from Customer c 
inner join (select license, count(*) as count_drives from Test_Drives group by license) td
on td.license = c.license
group by c.name, c.license
order by td.count_drives DESC

Open in new window

kayvey

ASKER

I was dubious on that syntax.. this looks like progress.. I tried, there's no such
thing as "bottom"..

umm.. I just Schtuck that top thing in my query. My query is good, but it fails to select
the "max" ..

take a look at
topSyntz.jpg

Kevin Cross

Did you look at my last suggestion? It should get you the max customer ...

kayvey

ASKER

You've changed all the variables. It gets me the LEAST prolific test driver.

kayvey

ASKER

Don't be afraid to hit return. inner join is not needed.
qry12.jpg

kayvey

ASKER

Okay these points are in the bag, but take a stab at this question:

https://www.experts-exchange.com/questions/23873127/JDBC-ODBC-interface-MS-SQL-Server.html?anchorAnswerId=22873286#a22873286

to claim your points

kayvey