mysql - How can I find which category has the most products shipped and the net income from sales in that category?

Question

Using the w3schools.com SQL tutorial Northwind database, I'm trying to display the category that has the most products shipped. Additionally, I want to display the net income from all sales in that category. I can't figure out how to take the category with the most products shipped, and use the amount of products shipped to calculate the net income of that category. This is because there are many different products that have the same CategoryID but different prices.

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS orderdetails;

CREATE TABLE categories
(CategoryID SERIAL PRIMARY KEY
,CategoryName VARCHAR(20) NOT NULL UNIQUE
,Description VARCHAR(100) NOT NULL
);

INSERT INTO categories VALUES
(1,'Beverages','Soft drinks, coffees, teas, beers, and ales'), 
(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings'), 
(3,'Confections','Desserts, candies, and sweet breads'), 
(4,'Dairy Products','Cheeses'), 
(5,'Grains/Cereals','Breads, crackers, pasta, and cereal'), 
(6,'Meat/Poultry','Prepared meats'), 
(7,'Produce','Dried fruit and bean curd'), 
(8,'Seafood','Seaweed and fish');


CREATE TABLE products
(ProductID SERIAL PRIMARY KEY
,ProductName VARCHAR(50) NOT NULL UNIQUE
,SupplierID INT NOT NULL
,CategoryID INT NOT NULL
,Unit VARCHAR(100) NOT NULL
,Price DECIMAL(5,2) NOT NULL
);

INSERT INTO products VALUES
( 1,"Chais",1,1,"10 boxes x 20 bags"                ,'18'), 
( 2,"Chang",1,1,"24 - 12 oz bottles"                ,'19'), 
( 3,"Aniseed Syrup",1,2,"12 - 550 ml bottles"           ,'10'), 
( 4,"Chef Anton's Cajun Seasoning",2,2,"48 - 6 oz jars"     ,'22'), 
( 5,"Chef Anton's Gumbo Mix",2,2,"36 boxes"         ,'21.35'), 
( 6,"Grandma's Boysenberry Spread",3,2,"12 - 8 oz jars"     ,'25'), 
( 7,"Uncle Bob's Organic Dried Pears",3,7,"12 - 1 lb pkgs." ,'30'), 
( 8,"Northwoods Cranberry Sauce",3,2,"12 - 12 oz jars"      ,'40'), 
( 9,"Mishi Kobe Niku",4,6,"18 - 500 g pkgs."            ,'97'), 
(10,"Ikura",4,8,"12 - 200 ml jars"              ,'31'), 
(11,"Queso Cabrales",5,4,"1 kg pkg."                ,'21'), 
(12,"Queso Manchego La Pastora",5,4,"10 - 500 g pkgs."      ,'38'), 
(13,"Konbu",6,8,"2 kg box"                  ,'6'), 
(14,"Tofu",6,7,"40 - 100 g pkgs."               ,'23.25'), 
(15,"Genen Shouyu",6,2,"24 - 250 ml bottles"            ,'15.5'), 
(16,"Pavlova",7,3,"32 - 500 g boxes"                ,'17.45'), 
(17,"Alice Mutton",7,6,"20 - 1 kg tins"             ,'39'), 
(18,"Carnarvon Tigers",7,8,"16 kg pkg."             ,'62.5'), 
(19,"Teatime Chocolate Biscuits",8,3,"10 boxes x 12 pieces" ,'9.2'), 
(20,"Sir Rodney's Marmalade",8,3,"30 gift boxes"        ,'81'), 
(21,"Sir Rodney's Scones",8,3,"24 pkgs. x 4 pieces"     ,'10'), 
(22,"Gustaf's Kn?ckebr?d",9,5,"24 - 500 g pkgs."        ,'21'), 
(23,"Tunnbr?d",9,5,"12 - 250 g pkgs.",'9'), 
(24,"Guaraná Fantástica",10,1,"12 - 355 ml cans",'4.5'), 
(25,"NuNuCa Nu?-Nougat-Creme",11,3,"20 - 450 g glasses",'14'), 
(26,"Gumb?r Gummib?rchen",11,3,"100 - 250 g bags",'31.23'), 
(27,"Schoggi Schokolade",11,3,"100 - 100 g pieces",'43.9'), 
(28,"R?ssle Sauerkraut",12,7,"25 - 825 g cans",'45.6'), 
(29,"Thüringer Rostbratwurst",12,6,"50 bags x 30 sausgs.",'123.79'), 
(30,"Nord-Ost Matjeshering",13,8,"10 - 200 g glasses",'25.89'), 
(31,"Gorgonzola Telino",14,4,"12 - 100 g pkgs",'12.5'), 
(32,"Mascarpone Fabioli",14,4,"24 - 200 g pkgs.",'32'), 
(33,"Geitost",15,4,"500 g",'2.5'), 
(34,"Sasquatch Ale",16,1,"24 - 12 oz bottles",'14'), 
(35,"Steeleye Stout",16,1,"24 - 12 oz bottles",'18'), 
(36,"Inlagd Sill",17,8,"24 - 250 g jars",'19'), 
(37,"Gravad lax",17,8,"12 - 500 g pkgs.",'26'), 
(38,"C?te de Blaye",18,1,"12 - 75 cl bottles",'263.5'), 
(39,"Chartreuse verte",18,1,"750 cc per bottle",'18'), 
(40,"Boston Crab Meat",19,8,"24 - 4 oz tins",'18.4'), 
(41,"Jack's New England Clam Chowder",19,8,"12 - 12 oz cans",'9.65'), 
(42,"Singaporean Hokkien Fried Mee",20,5,"32 - 1 kg pkgs.",'14'), 
(43,"Ipoh Coffee",20,1,"16 - 500 g tins",'46'), 
(44,"Gula Malacca",20,2,"20 - 2 kg bags",'19.45'), 
(45,"R?gede sild",21,8,"1k pkg.",'9.5'), 
(46,"Spegesild",21,8,"4 - 450 g glasses",'12'), 
(47,"Zaanse koeken",22,3,"10 - 4 oz boxes",'9.5'), 
(48,"Chocolade",22,3,"10 pkgs.",'12.75'), 
(49,"Maxilaku",23,3,"24 - 50 g pkgs.",'20'), 
(50,"Valkoinen suklaa",23,3,"12 - 100 g bars",'16.25'),
(51,"Manjimup Dried Apples",24,7,"50 - 300 g pkgs.",'53'),
(52,"Filo Mix",24,5,"16 - 2 kg boxes",'7'), 
(53,"Perth Pasties",24,6,"48 pieces",'32.8'), 
(54,"Tourtière",25,6,"16 pies",'7.45'), 
(55,"Paté chinois",25,6,"24 boxes x 2 pies",'24'), 
(56,"Gnocchi di nonna Alice",26,5,"24 - 250 g pkgs.",'38'), 
(57,"Ravioli Angelo",26,5,"24 - 250 g pkgs.",'19.5'), 
(58,"Escargots de Bourgogne",27,8,"24 pieces",'13.25'), 
(59,"Raclette Courdavault",28,4,"5 kg pkg.",'55'), 
(60,"Camembert Pierrot",28,4,"15 - 300 g rounds",'34'),
(61,"Sirop d'érable",29,2,"24 - 500 ml bottles",'28.5'), 
(62,"Tarte au sucre",29,3,"48 pies",'49.3'), 
(63,"Vegie-spread",7,2,"15 - 625 g jars",'43.9'), 
(64,"Wimmers gute Semmelkn?del",12,5,"20 bags x 4 pieces",'33.25'), 
(65,"Louisiana Fiery Hot Pepper Sauce",2,2,"32 - 8 oz bottles",'21.05'), 
(66,"Louisiana Hot Spiced Okra",2,2,"24 - 8 oz jars",'17'), 
(67,"Laughing Lumberjack Lager",16,1,"24 - 12 oz bottles",'14'), 
(68,"Scottish Longbreads",8,3,"10 boxes x 8 pieces",'12.5'), 
(69,"Gudbrandsdalsost",15,4,"10 kg pkg.",'36'), 
(70,"Outback Lager",7,1,"24 - 355 ml bottles",'15'), 
(71,"Fl?temysost",15,4,"10 - 500 g pkgs.",'21.5'), 
(72,"Mozzarella di Giovanni",14,4,"24 - 200 g pkgs.",'34.8'), 
(73,"R?d Kaviar",17,8,"24 - 150 g jars",'15'), 
(74,"Longlife Tofu",4,7,"5 kg pkg.",'10'), 
(75,"Rh?nbr?u Klosterbier",12,1,"24 - 0.5 l bottles",'7.75'), 
(76,"Lakkalik??ri",23,1,"500 ml",'18'), 
(77,"Original Frankfurter grüne So?e",12,2,"12 boxes",'13'); 
   
CREATE TABLE orderdetails
(OrderDetailID SERIAL PRIMARY KEY
,OrderID INT NOT NULL
,ProductID INT NOT NULL
,Quantity INT NOT NULL
);

INSERT INTO orderdetails VALUES
(1,10248,11,12),
(2,10248,42,10 ),
(  3,10248,72, 5 ),
(  4,10249,14, 9 ),
(  5,10249,51,40 ),
(  6,10250,41,10 ),
(  7,10250,51,35 ),
(  8,10250,65,15 ),
(  9,10251,22, 6 ),
( 10,10251,57,15 ),
( 11,10251,65,20 ),
( 12,10252,20,40 ),
( 13,10252,33,25 ),
( 14,10252,60,40 ),
( 15,10253,31,20 ),
( 16,10253,39,42 ),
( 17,10253,49,40 ),
( 18,10254,24,15 ),
( 19,10254,55,21 ),
( 20,10254,74,21 ),
( 21,10255, 2,20 ),
( 22,10255,16,35 ),
( 23,10255,36,25 ),
( 24,10255,59,30 ),
( 25,10256,53,15 ),
( 26,10256,77,12 ),
( 27,10257,27,25 ),
( 28,10257,39, 6 ),
( 29,10257,77,15 ),
( 30,10258, 2,50 ),
( 31,10258, 5,65 ),
( 32,10258,32, 6 ),
( 33,10259,21,10 ),
( 34,10259,37, 1 ),
( 35,10260,41,16 ),
( 36,10260,57, 50 ),
( 37,10260,62, 15 ),
( 38,10260,70, 21 ),
( 39,10261,21, 20 ),
( 40,10261,35, 20 ),
( 41,10262, 5, 12 ),
( 42,10262, 7, 15 ),
( 43,10262,56,  2 ),
( 44,10263,16, 60 ),
( 45,10263,24, 28 ),
( 46,10263,30, 60 ),
( 47,10263,74, 36 ),
( 48,10264, 2, 35 ),
( 49,10264,41, 25 ),
( 50,10265,17, 30 ),
( 51,10265,70, 20 ),
( 52,10266,12, 12 ),
( 53,10267,40, 50 ),
( 54,10267,59, 70 ),
( 55,10267,76, 15 ),
( 56,10268,29, 10 ),
( 57,10268,72,  4 ),
( 58,10269,33, 60 ),
( 59,10269,72, 20 ),
( 60,10270,36, 30 ),
( 61,10270,43, 25 ),
( 62,10271,33, 24 ),
( 63,10272,20,  6 ),
( 64,10272,31, 40 ),
( 65,10272,72, 24 ),
( 66,10273,10, 24 ),
( 67,10273,31, 15 ),
( 68,10273,33, 20 ),
( 69,10273,40, 60 ),
( 70,10273,76, 33 ),
( 71,10274,71, 20 ),
( 72,10274,72,  7 ),
( 73,10275,24, 12 ),
( 74,10275,59,  6 ),
( 75,10276,10, 15 ),
( 76,10276,13, 10 ),
( 77,10277,28, 20 ),
( 78,10277,62, 12 ),
( 79,10278,44, 16 ),
( 80,10278,59, 15 ),
( 81,10278,63,  8 ),
( 82,10278,73, 25 ),
( 83,10279,17, 15 ),
( 84,10280,24, 12 ),
( 85,10280,55, 20 ),
( 86,10280,75, 30 ),
( 87,10281,19,  1 ),
( 88,10281,24,  6 ),
( 89,10281,35,  4 ),
( 90,10282,30,  6 ),
( 91,10282,57,  2 ),
( 92,10283,15, 20 ),
( 93,10283,19, 18 ),
( 94,10283,60, 35 ),
( 95,10283,72,  3 ),
( 96,10284,27, 15 ),
( 97,10284,44, 21 ),
( 98,10284,60, 20 ),
( 99,10284,67,  5 ),
(100,10285, 1, 45 ),
(101,10285,40, 40 ),
(102,10285,53, 36 ),
(103,10286,35,100 ),
(104,10286,62, 40 ),
(105,10287,16, 40 ),
(106,10287,34, 20 ),
(107,10287,46, 15 ),
(108,10288,54, 10 ),
(109,10288,68,  3 ),
(110,10289, 3, 30 ),
(111,10289,64,  9 ),
(112,10290, 5, 20 ),
(113,10290,29, 15 ),
(114,10290,49, 15 ),
(115,10290,77, 10 ),
(116,10291,13, 20 ),
(117,10291,44, 24 ),
(118,10291,51, 2 ),
(119,10292,20, 20 ),
(120,10293,18, 12 ),
(121,10293,24, 10 ),
(122,10293,63,  5 ),
(123,10293,75,  6 ),
(124,10294, 1, 18 ),
(125,10294,17, 15 ),
(126,10294,43, 15 ),
(127,10294,60, 21 ),
(128,10294,75,  6 ),
(129,10295,56,  4 ),
(130,10296,11, 12 ),
(131,10296,16, 30 ),
(132,10296,69, 15 ),
(133,10297,39, 60 ),
(134,10297,72, 20 ),
(135,10298, 2, 40 ),
(136,10298,36, 40 ),
(137,10298,59, 30 ),
(138,10298,62, 15 ),
(139,10299,19, 15 ),
(140,10299,70, 20 ),
(141,10300,66, 30 ),
(142,10300,68, 20 ),
(143,10301,40, 10 ),
(144,10301,56, 20 ),
(145,10302,17, 40 ),
(146,10302,28, 28 ),
(147,10302,43, 12 ),
(148,10303,40, 40 ),
(149,10303,65, 30 ),
(150,10303,68, 15 ),
(151,10304,49, 30 ),
(152,10304,59, 10 ),
(153,10304,71,  2 ),
(154,10305,18, 25 ),
(155,10305,29, 25 ),
(156,10305,39, 30 ),
(157,10306,30, 10 ),
(158,10306,53, 10 ),
(159,10306,54,  5 ),
(160,10307,62, 10 ),
(161,10307,68,  3 ),
(162,10308,69,  1 ),
(163,10308,70,  5 ),
(164,10309, 4, 20 ),
(165,10309, 6, 30 ),
(166,10309,42,  2 ),
(167,10309,43, 20 ),
(168,10309,71,  3 ),
(169,10310,16, 10 ),
(170,10310,62,  5 ),
(171,10311,42,  6 ),
(172,10311,69,  7 ),
(173,10312,28,  4 ),
(174,10312,43, 24 ),
(175,10312,53, 20 ),
(176,10312,75, 10 ),
(177,10313,36, 12 ),
(178,10314,32, 40 ),
(179,10314,58, 30 ),
(180,10314,62, 25 ),
(181,10315,34, 14 ),
(182,10315,70, 30 ),
(183,10316,41, 10 ),
(184,10316,62, 70 ),
(185,10317, 1, 20 ),
(186,10318,41, 20 ),
(187,10318,76,  6 ),
(188,10319,17,  8 ),
(189,10319,28, 14 ),
(190,10319,76, 30 ),
(191,10320,71, 30 ),
(192,10321,35, 10 ),
(193,10322,52, 20 ),
(194,10323,15,  5 ),
(195,10323,25,  4 ),
(196,10323,39,  4 ),
(197,10324,16, 21 ),
(198,10324,35, 70 ),
(199,

Answer 1

So first of all you get the income for each product and category and then based on that you find total income for that category and you do this with the help of subquery, then you join this resultant table with the category table and with the help of group by you find the product count and total income for each category, below is the sql query for more indepth understanding

SELECT ct.CategoryID 
     , ct.CategoryName
     , SUM(pdd.ProductQuantityShipped) AS ProductsShipped
     , SUM(pdd.product_income) AS CategoryIncome 
  FROM Categories AS ct 
  INNER JOIN 
     ( SELECT pd.ProductID AS productId
            , pd.CategoryID
            , SUM(odt.Quantity) AS ProductQuantityShipped
            , SUM(pd.Price * odt.Quantity) AS product_income 
         FROM Products AS pd 
         INNER JOIN OrderDetails AS odt 
           ON pd.ProductID = odt.ProductID 
        GROUP 
           BY pd.ProductID
            , pd.CategoryID
     ) AS pdd
    ON ct.CategoryID = pdd.CategoryID
 GROUP 
    BY ct.CategoryID
     , ct.CategoryName
 ORDER 
    BY SUM(pdd.ProductQuantityShipped) DESC;

I am assuming you are using mysql as you mentioned it in question tags , you can use 'limit' and 'offset' for getting the desired range of output i.e 'LIMIT n' is used for limiting the row count to 'n' in output and 'OFFSET n' is used for skipping first 'n' rows. see the below updated code for getting the output for category having highest product shipped

SELECT ct.CategoryID 
     , ct.CategoryName
     , SUM(pdd.ProductQuantityShipped) AS ProductsShipped
     , SUM(pdd.product_income) AS CategoryIncome 
  FROM Categories AS ct 
  INNER JOIN 
     ( SELECT pd.ProductID AS productId
            , pd.CategoryID
            , SUM(odt.Quantity) AS ProductQuantityShipped
            , SUM(pd.Price * odt.Quantity) AS product_income 
         FROM Products AS pd 
         INNER JOIN OrderDetails AS odt 
           ON pd.ProductID = odt.ProductID 
        GROUP 
           BY pd.ProductID
            , pd.CategoryID
     ) AS pdd
    ON ct.CategoryID = pdd.CategoryID
 GROUP 
    BY ct.CategoryID
     , ct.CategoryName
 ORDER 
    BY SUM(pdd.ProductQuantityShipped) DESC
 LIMIT 1 OFFSET 0;

If you want to run it on mssql server that is used by w3-school that you mentioned, then you need to use 'TOP' for limiting the row count

SELECT TOP 1
       ct.CategoryID 
     , ct.CategoryName
     , SUM(pdd.ProductQuantityShipped) AS ProductsShipped
     , SUM(pdd.product_income) AS CategoryIncome 
  FROM Categories AS ct 
  INNER JOIN 
     ( SELECT pd.ProductID AS productId
            , pd.CategoryID
            , SUM(odt.Quantity) AS ProductQuantityShipped
            , SUM(pd.Price * odt.Quantity) AS product_income 
         FROM Products AS pd 
         INNER JOIN OrderDetails AS odt 
           ON pd.ProductID = odt.ProductID 
        GROUP 
           BY pd.ProductID
            , pd.CategoryID
     ) AS pdd
    ON ct.CategoryID = pdd.CategoryID
 GROUP 
    BY ct.CategoryID
     , ct.CategoryName
 ORDER 
    BY SUM(pdd.ProductQuantityShipped) DESC;