CODEWITHSUNDEEP
sqlsql-servert-sql
AlisonGrey
AlisonGrey

Reputation: 507

Find names based on max number dynamically in SQL

I have a data set like this:

Table A:

Date           PlaceName              Partner             Money
2021-03-26      SITE A               PARTNER A           100
2021-04-26      SITE B               PARTNER A           200
2021-03-26      SITE A               PARTNER B           0
2021-04-26      SITE B               PARTNER B           230
2021-04-26      SITE B               PARTNER B           230
2021-03-26      SITE A               PARTNER C           0
2021-04-26      SITE B               PARTNER C           230
2021-04-26      SITE B               PARTNER C           230
...

What's the maximum number of Place on which a Partner has spent money and who are these Partners? Display only the Partners reaching this max number

I tried this:

select count(PlaceName) as num_of_sites, Partner
from (
    select distinct Place, Partner
    from TableA
    where Money > 0
) a
group by Partner
order by count(PlaceName) desc

But I feel like its not the right logic. What am I missing?

Upvotes: 0

Views: 71

Answers (1)

Andrew
Andrew

Reputation: 1596

There's really no need for your subquery, it works without it:

SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
FROM TableA a
WHERE a.Money > 0 
GROUP BY a.Partner
ORDER BY COUNT(DISTINCT a.PlaceName) desc

but it won't make any difference to have one because of how queries are optimized, so whichever is easier to read is best. With a sub query it looks like this:

SELECT b.* 
FROM (SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
    FROM TableA a
    WHERE a.Money > 0 
    GROUP BY a.Partner) b
ORDER BY b.num_of_sites desc

If you only want the name on the highest result(s) then you want to:

SELECT TOP 1 WITH TIES b.PARTNER 
FROM (SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
    FROM TableA a
    WHERE a.Money > 0 
    GROUP BY a.Partner) b
ORDER BY b.num_of_sites desc

Upvotes: 3

Related Questions