CODEWITHSUNDEEP
sql-server
kanishka
kanishka

Reputation: 89

SQL select highest sold item from group of items

i have bellow two tables

enter image description here

enter image description here

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)

Upvotes: 0

Views: 279

Answers (1)

JMabee
JMabee

Reputation: 2300

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

Upvotes: 1

Related Questions