Ðr Derpling Ðr Derpling - 10 months ago 36
SQL Question

Getting max value if a counted cookieID while showing the cookieID and showing it belongs to bakkerID

sorry for the weird title i have hard time putting the problem into words
but this is the problem im facing. so this is my table

BakkerID | CookieID
6534 1009
6534 1009
6534 50
1327 5
1327 8
1327 8

and im trying to get this result

bakkerID cookieID total
6534 1009 2
1327 8 2

so i want to count the cookieID and get the max value while also holding on to the cookieID and the bakkerID. but i only want the max result i tried this via een subqeury like this
SELECT MAX(cookie_count) AS total, bakkerId, cookieID
FROM ( SELECT COUNT(cookieID) AS cookie_count, bakkerId, cookieID
FROM bakkery
GROUP BY bakkerID, cookieID ) As cookie_counter_table
GROUP BY bakkerID, cookieID

but the result will be

bakkerID cookieID total
6534 1009 2
6534 50 1
1327 8 2
1327 5 1

now this does not work because of the group by but i also cant leave it out else i will get a error that its no in a group by. i tried using where or having but i this can result in duplicated bakkerID wich i dont want. so my qeustion is how do i stop the not max result from showing up or im i doing this all wrong ?

Answer Source

One method uses row_number() and conditional aggregation:

SELECT bakkerId, MAX(cookie_count),
       MAX(CASE WHEN seqnum = 1 THEN cookieID END) as max_cookieId
FROM (SELECT bakkerId, cookieID, COUNT(*) AS cookie_count,  
             ROW_NUMBER() OVER (PARTITION BY bakkerId ORDER BY COUNT(*) DESC) as seqnum
      FROM bakkery
      GROUP BY  bakkerID, cookieID
     ) bc
GROUP BY bakkerId;