SQL select highest sold item from group of items

Question

i have bellow two tables

so i need to ge the TOP 1 item with the highest totalsales for each item group

i have written the following query sum up total sales from each item but not sure how move from here

select IM.ItemCode, IM.ItemName, IM.ItemGroup, SUM(ID.LineTotal) as TotalSales
from InvoiceDetail ID
inner join ItemMaster IM
on IM.ItemCode = ID.ItemCode
group by IM.ItemCode, IM.ItemName, IM.ItemGroup

sample data as text

CREATE TABLE InvoiceHeader
    (InvoiceNo varchar(255),
    CustCode varchar(255),
    InvoiceDate date,
    primary key (InvoiceNo))

CREATE TABLE CustomerMaster
    (CustCode varchar(255),
    CustomerName varchar(255),
    primary key (CustCode))

CREATE TABLE InvoiceDetail
    (InvoiceNo varchar(255),
    ItemCode INT,
    Quantity INT,
    SalesPrice INT,
    LineTotal INT,
    foreign key (InvoiceNo) References InvoiceHeader(InvoiceNo))

CREATE TABLE ItemMaster
    (ItemCode INT,
    ItemName varchar(255),
    ItemGroup varchar(255))

INSERT INTO InvoiceHeader(InvoiceNo, CustCode, InvoiceDate) VALUES 
('INV001', 'A001', '2015-01-01'),
('INV002', 'B001', '2015-03-02'),
('INV003', 'B001', '2015-03-05')

INSERT INTO CustomerMaster(CustCode, CustomerName) VALUES 
('A001', 'ABC Pte Ltd'),
('B001', 'CDE Pte Ltd')

INSERT INTO ItemMaster(ItemCode, ItemName, ItemGroup) VALUES 
(1001, 'Laptop A1', 'Computer'),
(1002, 'PC A1', 'Computer'),
(1003, 'Mouse', 'Accessories'),
(1004, 'Keyboard', 'Accessories')

INSERT INTO InvoiceDetail(InvoiceNo, ItemCode, Quantity,SalesPrice, LineTotal) VALUES 
('INV001', 1001, 2, 3000, 6000),
('INV001', 1003, 2, 25, 50),
('INV001', 1004, 1, 30, 30),
('INV002', 1002, 10, 2500, 25000),
('INV002', 1003, 20, 20, 400),
('INV003', 1001, 1, 3000, 3000),
('INV003', 1002, 1, 2500, 2500),
('INV003', 1003, 1, 20, 20)

Does this answer your question? [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — dbaltor, Sep 05 '20 at 10:01
Item group within item code - you might as well drop the item group AND please add sample data as text (i can't be bothered to transcribe) — P.Salmon, Sep 05 '20 at 10:45

score 1 · Accepted Answer · answered Sep 05 '20 at 11:06

You can just add a ROW_NUMBER() window function to your query and then select the rows with the #1 row for each category:

If for some reason you would have identical sales amounts for a category for the period and you need all of the rows you could replace ROW_NUMBER with DENSE_RANK().

SELECT * 
FROM
(
    select IM.ItemCode, IM.ItemName, IM.ItemGroup, SUM(ID.LineTotal) as TotalSales,
    ROW_NUMBER() OVER(PARTITION BY IM.ItemGroup ORDER BY  SUM(ID.LineTotal) desc) RN
    from InvoiceDetail ID
    inner join ItemMaster IM
    on IM.ItemCode = ID.ItemCode
    group by IM.ItemCode, IM.ItemName, IM.ItemGroup
) T1 
WHERE T1.RN = 1

SQL select highest sold item from group of items

1 Answers1