[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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
0
kayvey
Asked:
kayvey
  • 5
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
kayveyAuthor Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
Did you look at my last suggestion?  It should get you the max customer ...
0
 
kayveyAuthor Commented:
You've changed all the variables.  It gets me the LEAST prolific test driver.
0
 
kayveyAuthor Commented:
Don't be afraid to hit return. inner join is not needed.
qry12.jpg
0
 
kayveyAuthor Commented:
Okay these points are in the bag, but take a stab at this question:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_23873127.html#a22873286

to claim your points
0
 
Kevin CrossChief Technology OfficerCommented:
Glad you got that working.

(YES, count_drives should be in the group by...woops!!)

If you have other open questions, I will take a look but looks like this one was answered.
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, td.count_drives
order by td.count_drives DESC

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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