2

I need to perform a query to get the candies that are most liked by kids and here's what I've got:

SELECT COUNT(*) as counts,candies.name
FROM candies 
INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
INNER JOIN kids ON kids.id = kid_candy.kid_id
GROUP BY candies.name

which would return:

counts  | name
--------+---------
  3     | snowbear
  3     | whiterabbit
  2     | lollipop

All I want to see would be just

counts  | name
--------+---------
  3     | snowbear
  3     | whiterabbit

So what should my query be?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ronzohan
  • 105
  • 1
  • 8

4 Answers4

3

So, first lets get the counts per candy name (tmp_table), then get the max count of all candies (max_cnt) and finally put it all together and get the candies from tmp_table which have a count equal to max(counts) from max_cnt table...

 with tmp_table AS (
    select COUNT(*) as counts,candies.name as c_name
    from candies 
    INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
    INNER JOIN kids ON kids.id = kid_candy.kid_id
    GROUP BY candies.name        
 ), 
 max_cnt AS (
    SELECT max(counts) as max_count from tmp_table
 )
 SELECT counts, c_name as candies
 FROM tmp_table 
 JOIN max_cnt on max_count = counts
Dan
  • 876
  • 5
  • 15
1

Assuming a classical n:m relationship between kids and candies like detailed here:

You should provide such details in your question.

SELECT c.id, kc.counts, c.name
FROM  (
   SELECT candy_id AS id, count(*) AS counts
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   kid_candy
   GROUP  BY 1
   ) kc
JOIN   candies c USING (id)
WHERE  kc.rnk = 1;

This should be considerably faster than a query with two CTEs and needless joins.

Major points

  • It is potentially wrong (and more expensive) to group by candies.name. The name might not be unique. Use the primary key column for that, which is probably candies.id

  • Assuming referential integrity we do not need to join to the table kids at all.

  • Since we need to inspect the whole table, it is faster to aggregate first and join to candies to get the name later.

  • You can run a window function over an aggregate function:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Try this

SELECT * FROM
(
   SELECT COUNT(*) AS Counts,candies.name FROM candies 
     INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
     INNER JOIN kids ON kids.id = kid_candy.kid_id
   GROUP BY candies.name
 ) T1 JOIN
SELECT MAX(counts) MaxCount FROM
(
   SELECT COUNT(*) AS Counts,candies.name FROM candies 
      INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
      INNER JOIN kids ON kids.id = kid_candy.kid_id
   GROUP BY candies.name
) T1 ON T1.counts =T2.MaxCount
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

Do a sub select

Select max(counts) 
    (select COUNT(*) as counts,candies.name from candies 
INNER JOIN kid_candy ON kid_candy.candy_id = candies.id
INNER JOIN kids ON kids.id = kid_candy.kid_id
GROUP BY candies.name)
Edward Manda
  • 559
  • 3
  • 7