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,"Pâté 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,10324,46, 30 ),
(200,10324,59, 40 ),
(201,10324,63, 80 ),
(202,10325, 6, 6 ),
(203,10325,13, 12 ),
(204,10325,14, 9 ),
(205,10325,31, 4 ),
(206,10325,72, 40 ),
(207,10326, 4, 24 ),
(208,10326,57, 16 ),
(209,10326,75, 50 ),
(210,10327, 2, 25 ),
(211,10327,11, 50 ),
(212,10327,30, 35 ),
(213,10327,58, 30 ),
(214,10328,59, 9 ),
(215,10328,65, 40 ),
(216,10328,68, 10 ),
(217,10329,19, 10 ),
(218,10329,30, 8 ),
(219,10329,38, 20 ),
(220,10329,56, 12 ),
(221,10330,26, 50 ),
(222,10330,72, 25 ),
(223,10331,54, 15 ),
(224,10332,18, 40 ),
(225,10332,42, 10 ),
(226,10332,47, 16 ),
(227,10333,14, 10 ),
(228,10333,21, 10 ),
(229,10333,71, 40 ),
(230,10334,52, 8 ),
(231,10334,68, 10 ),
(232,10335, 2, 7 ),
(233,10335,31, 25 ),
(234,10335,32, 6 ),
(235,10335,51, 48 ),
(236,10336, 4, 18 ),
(237,10337,23, 40 ),
(238,10337,26, 24 ),
(239,10337,36, 20 ),
(240,10337,37, 28 ),
(241,10337,72, 25 ),
(242,10338,17, 20 ),
(243,10338,30, 15 ),
(244,10339, 4, 10 ),
(245,10339,17, 70 ),
(246,10339,62, 28 ),
(247,10340,18, 20 ),
(248,10340,41, 12 ),
(249,10340,43, 40 ),
(250,10341,33, 8 ),
(251,10341,59, 9 ),
(252,10342, 2, 24 ),
(253,10342,31, 56 ),
(254,10342,36, 40 ),
(255,10342,55, 40 ),
(256,10343,64, 50 ),
(257,10343,68, 4 ),
(258,10343,76, 15 ),
(259,10344, 4, 35 ),
(260,10344, 8, 70 ),
(261,10345, 8, 70 ),
(262,10345,19, 80 ),
(263,10345,42, 9 ),
(264,10346,17, 36 ),
(265,10346,56, 20 ),
(266,10347,25, 10 ),
(267,10347,39, 50 ),
(268,10347,40, 4 ),
(269,10347,75, 6 ),
(270,10348, 1, 15 ),
(271,10348,23, 25 ),
(272,10349,54, 24 ),
(273,10350,50, 15 ),
(274,10350,69, 18 ),
(275,10351,38, 20 ),
(276,10351,41, 13 ),
(277,10351,44, 77 ),
(278,10351,65, 10 ),
(279,10352,24, 10 ),
(280,10352,54, 20 ),
(281,10353,11, 12 ),
(282,10353,38, 50 ),
(283,10354, 1, 12 ),
(284,10354,29, 4 ),
(285,10355,24, 25 ),
(286,10355,57, 25 ),
(287,10356,31, 30 ),
(288,10356,55, 12 ),
(289,10356,69, 20 ),
(290,10357,10, 30 ),
(291,10357,26, 16 ),
(292,10357,60, 8 ),
(293,10358,24, 10 ),
(294,10358,34, 10 ),
(295,10358,36, 20 ),
(296,10359,16, 56 ),
(297,10359,31, 70 ),
(298,10359,60, 80 ),
(299,10360,28, 30 ),
(300,10360,29, 35 ),
(301,10360,38, 10 ),
(302,10360,49, 35 ),
(303,10360,54, 28 ),
(304,10361,39, 54 ),
(305,10361,60, 55 ),
(306,10362,25, 50 ),
(307,10362,51, 20 ),
(308,10362,54, 24 ),
(309,10363,31, 20 ),
(310,10363,75, 12 ),
(311,10363,76, 12 ),
(312,10364,69, 30 ),
(313,10364,71, 5 ),
(314,10365,11, 24 ),
(315,10366,65, 5 ),
(316,10366,77, 5 ),
(317,10367,34, 36 ),
(318,10367,54, 18 ),
(319,10367,65, 15 ),
(320,10367,77, 7 ),
(321,10368,21, 5 ),
(322,10368,28, 13 ),
(323,10368,57, 25 ),
(324,10368,64, 35 ),
(325,10369,29, 20 ),
(326,10369,56, 18 ),
(327,10370, 1, 15 ),
(328,10370,64, 30 ),
(329,10370,74, 20 ),
(330,10371,36, 6 ),
(331,10372,20,12 ),
(332,10372,38,40 ),
(333,10372,60,70 ),
(334,10372,72,42 ),
(335,10373,58,80 ),
(336,10373,71,50 ),
(337,10374,31,30 ),
(338,10374,58,15 ),
(339,10375,14,15 ),
(340,10375,54,10 ),
(341,10376,31,42 ),
(342,10377,28,20 ),
(343,10377,39,20 ),
(344,10378,71,6 ),
(345,10379,41,8 ),
(346,10379,63,16 ),
(347,10379,65,20 ),
(348,10380,30,18 ),
(349,10380,53,20 ),
(350,10380,60,6 ),
(351,10380,70,30 ),
(352,10381,74,14 ),
(353,10382,5,32 ),
(354,10382,18,9 ),
(355,10382,29,14 ),
(356,10382,33,60 ),
(357,10382,74,50 ),
(358,10383,13,20 ),
(359,10383,50,15 ),
(360,10383,56,20 ),
(361,10384,20,28 ),
(362,10384,60,15 ),
(363,10385,7,10 ),
(364,10385,60,20 ),
(365,10385,68,8 ),
(366,10386,24,15 ),
(367,10386,34,10 ),
(368,10387,24,15 ),
(369,10387,28,6 ),
(370,10387,59,12 ),
(371,10387,71,15 ),
(372,10388,45,15 ),
(373,10388,52,20 ),
(374,10388,53,40 ),
(375,10389,10,16 ),
(376,10389,55,15 ),
(377,10389,62,20 ),
(378,10389,70,30 ),
(379,10390,31,60 ),
(380,10390,35,40 ),
(381,10390,46,45 ),
(382,10390,72,24 ),
(383,10391,13,18 ),
(384,10392,69,50 ),
(385,10393, 2,25 ),
(386,10393,14,42 ),
(387,10393,25,7 ),
(388,10393,26,70 ),
(389,10393,31,32 ),
(390,10394,13,10 ),
(391,10394,62,10 ),
(392,10395,46,28 ),
(393,10395,53,70 ),
(394,10395,69,8 ),
(395,10396,23,40 ),
(396,10396,71,60 ),
(397,10396,72,21 ),
(398,10397,21,10 ),
(399,10397,51,18 ),
(400,10398,35,30 ),
(401,10398,55,120 ),
(402,10399,68,60 ),
(403,10399,71,30 ),
(404,10399,76,35 ),
(405,10399,77,14 ),
(406,10400,29,21 ),
(407,10400,35,35 ),
(408,10400,49,30 ),
(409,10401,30,18 ),
(410,10401,56,70 ),
(411,10401,65,20 ),
(412,10401,71,60 ),
(413,10402,23,60 ),
(414,10402,63,65 ),
(415,10403,16,21 ),
(416,10403,48,70 ),
(417,10404,26,30 ),
(418,10404,42,40 ),
(419,10404,49,30 ),
(420,10405, 3,50 ),
(421,10406, 1,10 ),
(422,10406,21,30 ),
(423,10406,28,42 ),
(424,10406,36,5 ),
(425,10406,40,2 ),
(426,10407,11,30 ),
(427,10407,69,15 ),
(428,10407,71,15 ),
(429,10408,37,10 ),
(430,10408,54,6 ),
(431,10408,62,35 ),
(432,10409,14,12 ),
(433,10409,21,12 ),
(434,10410,33,49 ),
(435,10410,59,16 ),
(436,10411,41,25 ),
(437,10411,44,40 ),
(438,10411,59,9 ),
(439,10412,14,20 ),
(440,10413, 1,24 ),
(441,10413,62,40 ),
(442,10413,76,14 ),
(443,10414,19,18 ),
(444,10414,33,50 ),
(445,10415,17,2 ),
(446,10415,33,20 ),
(447,10416,19,20 ),
(448,10416,53,10 ),
(449,10416,57,20 ),
(450,10417,38,50 ),
(451,10417,46,2 ),
(452,10417,68,36 ),
(453,10417,77,35 ),
(454,10418, 2,60 ),
(455,10418,47,55 ),
(456,10418,61,16 ),
(457,10418,74,15 ),
(458,10419,60,60 ),
(459,10419,69,20 ),
(460,10420, 9,20 ),
(461,10420,13,2 ),
(462,10420,70,8 ),
(463,10420,73,20 ),
(464,10421,19,4 ),
(465,10421,26,30 ),
(466,10421,53,15 ),
(467,10421,77,10 ),
(468,10422,26,2 ),
(469,10423,31,14 ),
(470,10423,59,20 ),
(471,10424,35,60 ),
(472,10424,38,49 ),
(473,10424,68,30 ),
(474,10425,55,10 ),
(475,10425,76,20 ),
(476,10426,56,5 ),
(477,10426,64,7 ),
(478,10427,14,35 ),
(479,10428,46,20 ),
(480,10429,50,40 ),
(481,10429,63,35 ),
(482,10430,17,45 ),
(483,10430,21,50 ),
(484,10430,56,30 ),
(485,10430,59,70 ),
(486,10431,17,50 ),
(487,10431,40,50 ),
(488,10431,47,30 ),
(489,10432,26,10 ),
(490,10432,54,40 ),
(491,10433,56,28 ),
(492,10434,11,6 ),
(493,10434,76,18 ),
(494,10435,2,10 ),
(495,10435,22,12 ),
(496,10435,72,10 ),
(497,10436,46,5 ),
(498,10436,56,40 ),
(499,10436,64,30 ),
(500,10436,75,24 ),
(501,10437,53,15 ),
(502,10438,19,15 ),
(503,10438,34,20 ),
(504,10438,57,15 ),
(505,10439,12,15 ),
(506,10439,16,16 ),
(507,10439,64,6 ),
(508,10439,74,30 ),
(509,10440, 2,45 ),
(510,10440,16,49 ),
(511,10440,29,24 ),
(512,10440,61,90 ),
(513,10441,27,50 ),
(514,10442,11,30 ),
(515,10442,54,80 ),
(516,10442,66,60 ),
(517,10443,11,6 ),
(518,10443,28,12);
Database: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
So far, I'm summing the quantity of each order in OrderDetails, joining the Categories, OrderDetails and Products tables and grouping the results by CategoryID. Then, I thought I would order the table descending by the number of products shipped, select the category with the most products shipped and calculate the category's net income. I now realize this is the wrong way to approach this, because there are many different products under the same CategoryID that have different prices. I'm new to SQL, and I've done some digging around on Google to try and figure this out, but to no avail.
So far I've come up with this:
SELECT ct.CategoryID, ct.CategoryName, SUM(odt.Quantity) AS ProductsShipped
FROM Categories AS ct
INNER JOIN Products AS p ON p.CategoryID = ct.CategoryID
INNER JOIN OrderDetails AS odt ON odt.ProductID = p.ProductID
GROUP BY ct.CategoryID
ORDER BY ProductsShipped DESC;
The above code displays:
CategoryID CategoryName ProductsShipped
4 Dairy Products 2601
1 Beverages 2289
3 Confections 2110
8 Seafood 1445
2 Condiments 1383
6 Meat/Poultry 1288
5 Grains/Cereals 912
7 Produce 715
I feel like I'm somewhat on the right track, but I can feel that I need to retrace a little bit and implement something in the code before my GROUP BY command in order to calculate the net income of that category. Problem is I can't figure out what to code.
EDIT: Also, how can I select only the top entry in my table? I only want to display Dairy Products as it has the most products shipped.
