Select highest value based on aggregate function

I have a GiftSales table, it contains the id of the item (giftId), and the category of that item (categoryId) I need to get the best selling item for each category. Right now my query looks like this

SELECT giftId, categoryId, COUNT(giftId) as Total
FROM GiftSales
GROUP BY giftId, categoryId

And its giving me

==================================
|| giftId || categoryId || Total||
==================================
||    1   ||      1     ||   8  ||
==================================
||    2   ||      1     ||   5  ||
==================================
||   23   ||      2     ||   12 ||
==================================

I need to only show the highest value per each category, so basically, the table shouldn't contain the second item.

CodePudding user response：

Use a window function such as MAX OVER per category:

select giftid, categoryid, total
from
(
  select 
    giftid,
    categoryid,
    count(*) as total,
    max(count(*)) over (partition by categoryid) as category_max
  from giftsales
  group by giftid, categoryid
) aggregated
where total = category_max;

CodePudding user response：

I'd recommend using a window function, and dense_rank can be helpful when looking at top selling products by category as you may want to include any ties.

Schema (MySQL v8.0)

CREATE TABLE IDs (
  `gift_id` INTEGER,
  `category_id` INTEGER
);

INSERT INTO IDs
  (`gift_id`, `category_id`)
VALUES
  ('1', '1'),
  ('1', '1'),
  ('1', '1'),
  ('1', '1'),
  ('1', '1'),
  ('1', '1'),
  ('1', '1'),
  ('1', '1'),
  ('2', '1'),
  ('2', '1'),
  ('2', '1'),
  ('2', '1'),
  ('2', '1');

Query #1

select a.category_id,a.gift_id,a.total from (
select
category_id,
gift_id,
count(gift_id) as total,
dense_rank() over (partition by category_id order by count(gift_id) desc) as ranking
from IDs group by 1,2) as a where ranking = 1;

category_id	gift_id	total
1	1	8

View on DB Fiddle

CodePudding user response：

SELECT DISTINCT categoryId, MAX(Total) as total FROM(
  SELECT giftId, categoryId, COUNT(giftId) as Total FROM GiftSales GROUP BY giftId, categoryId
) AS T GROUP BY giftId, categoryId;

I got it working by using the distinct with the categoryId and since you need the total by category I removed the giftId and everything worked fine :) I used a playground to test this and the playground can be found here -> https://www.db-fiddle.com/f/qsGLKUZyos2ZKTftykkazd/0