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 […]
- Tags -102], -11, -12.75, -182, -23, -32, -38, -41, -442, -48, -7.75, .44, .53, .97], '123.79'), '13'); CREATE TABLE orderdetails (OrderDetailID SERIAL PRIMARY KEY, '137', '17.45'), '177', '21.05'), '21.35'), '23.25'), '263.5'), '31.23'), '43.9'), '505', '508', 'Breads, 'candies', 'Confections', 'crackers', 'Dairy Products', 'desserts', 'Dried fruit and bean curd'), 'Grains/Cereals', 'Meat/Poultry', 'Pasta', 'Prepared meats'), 'Seaweed and fish'); CREATE TABLE products (ProductID SERIAL PRIMARY KEY, 'Soft drinks, 'Sweet and savory sauces, 'tofu', "1 kg pkg.", "1":66, "10 - 200 g glasses", "10 - 4 oz boxes", "10 - 500 g pkgs.", "10 boxes x 12 pieces", "10 boxes x 20 bags", "10 boxes x 8 pieces", "10 kg pkg.", "10 pkgs.", "100 - 100 g pieces", "100 - 250 g bags", "12 - 1 lb pkgs.", "12 - 100 g bars", "12 - 100 g pkgs", "12 - 12 oz cans", "12 - 12 oz jars", "12 - 200 ml jars", "12 - 250 g pkgs.", "12 - 355 ml cans", "12 - 500 g pkgs.", "12 - 550 ml bottles", "12 - 75 cl bottles", "12 - 8 oz jars", "12 boxes", "14", "14":7, "15 - 300 g rounds", "15 - 625 g jars", "16 - 2 kg boxes", "16 - 500 g tins", "16 kg pkg.", "16 pies", "18 - 500 g pkgs.", "1k pkg.", "2 kg box", "2":70, "20 - 1 kg tins", "20 - 2 kg bags", "20 - 450 g glasses", "20 bags x 4 pieces", "24 - 0.5 l bottles", "24 - 12 oz bottles", "24 - 150 g jars", "24 - 200 g pkgs.", "24 - 250 g jars", "24 - 250 g pkgs.", "24 - 250 ml bottles", "24 - 355 ml bottles", "24 - 4 oz tins", "24 - 50 g pkgs.", "24 - 500 g pkgs.", "24 - 500 ml bottles", "24 - 8 oz jars", "24 boxes x 2 pies", "24 pieces", "24 pkgs. x 4 pieces", "25 - 825 g cans", "3":14, "30 gift boxes", "32 - 1 kg pkgs.", "32 - 500 g boxes", "32 - 8 oz bottles", "334", "335", "36 boxes", "4 - 450 g glasses", "4":14, "4":72, "4|1|8"], "4|3|9"], "40 - 100 g pkgs.", "48 - 6 oz jars", "48 pieces", "48 pies", "5 kg pkg.", "50 - 300 g pkgs.", "50 bags x 30 sausgs.", "500 g", "500 ml", "71", "750 cc per bottle", "Alice Mutton", "Aniseed Syrup", "Boston Crab Meat", "Camembert Pierrot", "Carnarvon Tigers", "Chais", "Chang", "Chartreuse verte", "Chef Anton's Cajun Seasoning", "Chef Anton's Gumbo Mix", "Chocolade", "Côte de Blaye", "Escargots de Bourgogne", "Filo Mix", "Fløtemysost", "Geitost", "Genen Shouyu", "Gnocchi di nonna Alice", "Gorgonzola Telino", "Grandma's Boysenberry Spread", "Gravad lax", "Guaraná Fantástica", "Gudbrandsdalsost", "Gula Malacca", "Gumbär Gummibärchen", "Gustaf's Knäckebröd", "Ikura", "Inlagd Sill", "Ipoh Coffee", "Jack's New England Clam Chowder", "Konbu", "Lakkalikööri", "Laughing Lumberjack Lager", "Longlife Tofu", "Louisiana Fiery Hot Pepper Sauce", "Louisiana Hot Spiced Okra", "Manjimup Dried Apples", "Mascarpone Fabioli", "Maxilaku", "Mishi Kobe Niku", "Mozzarella di Giovanni", "Nord-Ost Matjeshering", "Northwoods Cranberry Sauce", "NuNuCa Nuß-Nougat-Creme", "Original Frankfurter grüne Soße", "Outback Lager", "Pâté chinois", "Pavlova", "Perth Pasties", "Queso Cabrales", "Queso Manchego La Pastora", "Raclette Courdavault", "Ravioli Angelo", "Rhönbräu Klosterbier", "Röd Kaviar", "Røgede sild", "Rössle Sauerkraut", "Sasquatch Ale", "Schoggi Schokolade", "Scottish Longbreads", "Singaporean Hokkien Fried Mee", "Sir Rodney's Marmalade", "Sir Rodney's Scones", "Sirop d'érable", "Spegesild", "Steeleye Stout", "Tarte au sucre", "Teatime Chocolate Biscuits", "Thüringer Rostbratwurst", "Tourtière", "Tunnbröd", "Uncle Bob's Organic Dried Pears", "Valkoinen suklaa", "Vegie-spread", "Wimmers gute Semmelknödel", "Zaanse koeken", ('coffees', (338, (388, (444, (475, (489, (64, [ "103", [ 55, [ 75, [ 82, ["4|6|5", [[ 87, [[496., [109, [132, [148, [151, [2.5, [56, [CategoryID] INT NOT NULL, [OrderID] [int] NOT NULL, }, };, 1, 10, 100, 101, 10248, 10249, 10250, 10251, 10252, 10253, 10254, 10255, 10256, 10257, 10258, 10259, 10260, 10261, 10262, 10263, 10264, 10265, 10266, 10267, 10268, 10269, 10270, 10271, 10272, 10273, 10274, 10275, 10276, 10277, 10278, 10279, 10280, 10281, 10282, 10283, 10284, 10285, 10286, 10287, 10288, 10289, 10290, 10291, 10292, 10293, 10294, 10295, 10296, 10297, 10298, 10299, 10300, 10301, 10302, 10303, 10304, 10305, 10306, 10307, 10308, 10309, 10310, 10311, 10312, 10313, 10314, 10315, 10316, 10317, 10318, 10319, 10320, 10321, 10322, 10323, 10324, 10325, 10326, 10327, 10328, 10329, 10330, 10331, 10332, 10333, 10334, 10335, 10336, 10337, 10338, 10339, 10340, 10341, 10342, 10343, 10344, 10345, 10346, 10347, 10348, 10349, 10350, 10351, 10352, 10353, 10354, 10355, 10356, 10357, 10358, 10359, 10360, 10361, 10362, 10363, 10364, 10365, 10366, 10367, 10368, 10369, 10370, 10371, 10372, 10373, 10374, 10375, 10376, 10377, 10378, 10379, 10380, 10381, 10382, 10383, 10384, 10385, 10386, 10387, 10388, 10389, 10390, 10391, 10392, 10393, 10394, 10395, 10396, 10397, 10398, 104, 10400, 10401, 10402, 10403, 10404, 10405, 10406, 10407, 10408, 10409, 10410, 10411, 10412, 10413, 10414, 10415, 10416, 10417, 10418, 10419, 10420, 10421, 10422, 10423, 10424, 10425, 10426, 10427, 10428, 10429, 10430, 10431, 10432, 10433, 10434, 10435, 10436, 10437, 10438, 10439, 10440, 10441, 10442, 10443, 105, 106]]], 107, 108, 110, 111], 112, 113, 114, 115, 116, 117, 118, 119, 12, 12) = 5, 12); Database: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all So far, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 13, 13.25, 130, 131, 133, 134, 135, 136, 138, 139, 140, 141, 142, 143, 144, 145, 146, 149, 15, 15.5]", 150, 152], 153, 154, 155], 156, 157, 158, 159, 16, 16.25, 160, 161, 162, 163], 164, 165, 167, 168], 169, 17, 170, 171, 172, 173, 174, 175, 176], 178, 179, 18, 18 4, 180, 181, 183, 184, 185, 186, 187, 188, 189, 19, 19.45, 19.5], 190, 191, 192, 193, 194, 195, 196, 197, 198], 199, 2, 2) NOT NULL ); INSERT INTO products VALUES ( 1, 20, 200, 201, 202, 203, 204, 205, 206, 207, 208], 209, 21, 21.5), 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 22, 220, 221, 222]], 223, 224, 225, 226, 227, 228, 229, 230, 231, 232)' } ] }, 234], 235, 236, 237, 238, 239, 24, 240, 241, 242], 243, 244, 245, 246, 247, 248, 249, 25, 25.89, 250, 251, 252], 253], 254, 255, 256, 257, 258, 259, 26, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 27, 271, 272, 273, 274, 275, 276, 277, 278, 279, 28, 28.5], 280, 281, 282, 283, 284, 285, 286]], 287, 288, 289, 29, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 3, 30, 300, 301), 302], 303, 304, 305, 306, 307, 308, 309, 31, 310, 311, 312, 313, 315, 316, 317, 318, 319, 32] [8, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 33, 33.25, 330, 331, 332, 333, 336), 337), 339), 34, 34.8, 340, 341, 342), 343, 344, 345, 346, 347, 348, 349, 35, 350, 351, 352" }, 353, 354, 355, 356, 357, 358, 359, 36, 360, 361, 362, 363, 364, 365, 366), 367, 368, 369, 37, 370, 371, 372, 373, 374, 375, 376, 377, 378" }, 379, 380, 381, 382, 383, 384, 385, 386", 387, 389, 39, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 4, 4.5, 40, 400, 401, 402" }, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 415), 416, 417, 419, 42, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429 ], 43, 430, 431, 432, 433, 434, 435, 436, 437, 438, 440, 441, 443, 445, 446, 447, 448, 449, 45.6, 45], 450), 451, 452, 453, 454, 455, 456, 457, 458, 459, 46, 460, 461, 462, 463, 464, 466, 467, 468, 469" }, 47, 470, 471 ], 473, 474, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 49, 49.3, 490, 491, 492, 493, 494, 495, 497, 498, 499), 5, 5:10], 50, 500), 501, 502, 503, 504, 506, 507, 509, 51, 511, 512)), 513, 514, 515, 516, 517, 518, 52, 54, 57, 58, 59, 6, 60, 61, 62, 62.5, 63, 65, 66, 67, 68, 69, 7, 7.45, 70], 72, 73, 74, 76, 77, 78, 79, 8, 80, 81, 83, 84), 85, 86, 88, 89, 9, 9.5, 9.65, 9] { { 2, 90, 91, 92, 93, 94, 95, 96, 98, 99], and ales'), and cereal'), and I've done some digging around on Google to try and figure this out, and seasonings, and sweet breads'), and use the amount of products shipped to calculate the net income of that category. This is because there are many different products that h, because there are many different products under the same CategoryID that have different prices. I'm new to SQL, beers)), Beverages, 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 n, but to no avail. So far I've come up with this: SELECT Categories.CategoryID, Categories.CategoryName, CategoryName VARCHAR(20) NOT NULL UNIQUE, cheeses, Condiments, Description VARCHAR(100) NOT NULL ); INSERT INTO categories VALUES (1, I thought I would order the table descending by the number of products shipped, 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, I'm summing the quantity of each order in OrderDetails, I'm trying to display the category that has the most products shipped. Additionally, joining the Categories, OrderDetails and Products tables and grouping the results by CategoryID. Then, Price DECIMAL(5, produce, ProductId INT NOT NULL, ProductName VARCHAR(50) NOT NULL UNIQUE, Quantity INT NOT NULL ); INSERT INTO orderdetails VALUES (1, relishes, seafood, 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 t, spreads, SUM(OrderDetails.Quantity) AS [Products Shipped] FROM ((Categories INNER JOIN Products ON Products.CategoryID = Categories.CategoryID) INNER, SupplierID INT NOT NULL, teas, Unit VARCHAR(100) NOT NULL, Using the w3schools.com SQL tutorial Northwind database