BEGIN; BEGIN select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%ivory%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262531214.932295 ALGERIA | 1997 | 457956597.232568 ALGERIA | 1996 | 461319567.653361 ALGERIA | 1995 | 461420740.965933 ALGERIA | 1994 | 459610804.010544 ALGERIA | 1993 | 454546550.586011 ALGERIA | 1992 | 469071708.934111 ARGENTINA | 1998 | 267572614.586526 ARGENTINA | 1997 | 455996058.036232 ARGENTINA | 1996 | 459683683.668222 ARGENTINA | 1995 | 453992304.027396 ARGENTINA | 1994 | 454982478.339469 ARGENTINA | 1993 | 458891673.900776 ARGENTINA | 1992 | 459817409.793955 BRAZIL | 1998 | 272579846.879073 BRAZIL | 1997 | 468384764.666909 BRAZIL | 1996 | 473714643.899583 BRAZIL | 1995 | 463161944.739259 BRAZIL | 1994 | 463069709.66694 BRAZIL | 1993 | 464307506.073256 BRAZIL | 1992 | 471670610.365326 CANADA | 1998 | 272437937.503827 CANADA | 1997 | 469424668.621647 CANADA | 1996 | 465269945.472736 CANADA | 1995 | 461167321.919443 CANADA | 1994 | 463418783.731689 CANADA | 1993 | 476564640.134382 CANADA | 1992 | 461677432.958912 CHINA | 1998 | 277384544.012146 CHINA | 1997 | 466755403.096504 CHINA | 1996 | 465439169.433475 CHINA | 1995 | 469067425.473274 CHINA | 1994 | 473488164.382024 CHINA | 1993 | 471562793.446231 CHINA | 1992 | 468807613.456299 EGYPT | 1998 | 277322850.474867 EGYPT | 1997 | 462528412.067252 EGYPT | 1996 | 463082736.874861 EGYPT | 1995 | 461365487.714311 EGYPT | 1994 | 458705329.782596 EGYPT | 1993 | 459634941.18004 EGYPT | 1992 | 467232729.957642 ETHIOPIA | 1998 | 275918246.53251 ETHIOPIA | 1997 | 459843250.02819 ETHIOPIA | 1996 | 464501819.593689 ETHIOPIA | 1995 | 458348963.982449 ETHIOPIA | 1994 | 463944082.090877 ETHIOPIA | 1993 | 457622139.211256 ETHIOPIA | 1992 | 457246591.060604 FRANCE | 1998 | 272274934.824924 FRANCE | 1997 | 455165255.227084 FRANCE | 1996 | 456962352.956004 FRANCE | 1995 | 460303693.659079 FRANCE | 1994 | 456167658.884076 FRANCE | 1993 | 455302953.576064 FRANCE | 1992 | 450413467.399038 GERMANY | 1998 | 275686375.730886 GERMANY | 1997 | 472649106.566595 GERMANY | 1996 | 471594010.793916 GERMANY | 1995 | 468781457.086433 GERMANY | 1994 | 469562472.073823 GERMANY | 1993 | 465983059.700175 GERMANY | 1992 | 469631724.411439 INDIA | 1998 | 278984905.96608 INDIA | 1997 | 478409775.830148 INDIA | 1996 | 473507427.333152 INDIA | 1995 | 474856285.96177 INDIA | 1994 | 474628823.785739 INDIA | 1993 | 476268541.231123 INDIA | 1992 | 482386322.893863 INDONESIA | 1998 | 266224262.958038 INDONESIA | 1997 | 454587155.103541 INDONESIA | 1996 | 463275021.862561 INDONESIA | 1995 | 455305181.970261 INDONESIA | 1994 | 457731004.257939 INDONESIA | 1993 | 463017487.019711 INDONESIA | 1992 | 461328447.001143 IRAN | 1998 | 267331024.962193 IRAN | 1997 | 466891935.087016 IRAN | 1996 | 468512342.434949 IRAN | 1995 | 464702589.503009 IRAN | 1994 | 461488716.722785 IRAN | 1993 | 463465890.291366 IRAN | 1992 | 465384566.275393 IRAQ | 1998 | 279215929.511963 IRAQ | 1997 | 469241681.357122 IRAQ | 1996 | 467293916.763114 IRAQ | 1995 | 463206829.859006 IRAQ | 1994 | 465254102.324917 IRAQ | 1993 | 467204980.768885 IRAQ | 1992 | 470040459.66405 JAPAN | 1998 | 269873321.82197 JAPAN | 1997 | 465359075.117225 JAPAN | 1996 | 470112088.656814 JAPAN | 1995 | 462196787.463324 JAPAN | 1994 | 469007608.960367 JAPAN | 1993 | 469075404.995613 JAPAN | 1992 | 461715087.908943 JORDAN | 1998 | 270429253.576282 JORDAN | 1997 | 460138866.167385 JORDAN | 1996 | 460417079.036856 JORDAN | 1995 | 461908036.82935 JORDAN | 1994 | 459454738.031518 JORDAN | 1993 | 457376865.270423 JORDAN | 1992 | 459504688.560229 KENYA | 1998 | 279660137.252536 KENYA | 1997 | 468472807.179232 KENYA | 1996 | 471951749.433782 KENYA | 1995 | 470552210.565125 KENYA | 1994 | 480220945.901683 KENYA | 1993 | 466058837.088851 KENYA | 1992 | 477921498.918306 MOROCCO | 1998 | 270588564.31834 MOROCCO | 1997 | 459815862.561236 MOROCCO | 1996 | 461998367.711336 MOROCCO | 1995 | 460555678.045904 MOROCCO | 1994 | 460646499.277892 MOROCCO | 1993 | 462326090.073134 MOROCCO | 1992 | 460625170.449827 MOZAMBIQUE | 1998 | 268681877.168117 MOZAMBIQUE | 1997 | 456178893.608831 MOZAMBIQUE | 1996 | 463987026.913134 MOZAMBIQUE | 1995 | 457188549.377041 MOZAMBIQUE | 1994 | 451939957.960489 MOZAMBIQUE | 1993 | 465413963.14552 MOZAMBIQUE | 1992 | 462457285.560106 PERU | 1998 | 275404947.496844 PERU | 1997 | 476211985.859384 PERU | 1996 | 473261230.73261 PERU | 1995 | 478688164.906716 PERU | 1994 | 468037709.904686 PERU | 1993 | 467373890.100837 PERU | 1992 | 469121158.199355 ROMANIA | 1998 | 272299921.235834 ROMANIA | 1997 | 459623797.59557 ROMANIA | 1996 | 471615182.400296 ROMANIA | 1995 | 466918205.896884 ROMANIA | 1994 | 465041225.292277 ROMANIA | 1993 | 456847015.718072 ROMANIA | 1992 | 467028219.520168 RUSSIA | 1998 | 274658318.855187 RUSSIA | 1997 | 467392990.33297 RUSSIA | 1996 | 473417720.492234 RUSSIA | 1995 | 469640551.695669 RUSSIA | 1994 | 466549348.56621 RUSSIA | 1993 | 474823915.320698 RUSSIA | 1992 | 466031407.561983 SAUDI ARABIA | 1998 | 266191839.355201 SAUDI ARABIA | 1997 | 470549257.362947 SAUDI ARABIA | 1996 | 467709809.503753 SAUDI ARABIA | 1995 | 465730491.21065 SAUDI ARABIA | 1994 | 472496105.968116 SAUDI ARABIA | 1993 | 464853154.716046 SAUDI ARABIA | 1992 | 477111118.159115 UNITED KINGDOM | 1998 | 266803833.214979 UNITED KINGDOM | 1997 | 454363624.187142 UNITED KINGDOM | 1996 | 460224731.16426 UNITED KINGDOM | 1995 | 455149421.535324 UNITED KINGDOM | 1994 | 455788240.986598 UNITED KINGDOM | 1993 | 454383363.148878 UNITED KINGDOM | 1992 | 462303496.064644 UNITED STATES | 1998 | 273028253.192887 UNITED STATES | 1997 | 468871410.555702 UNITED STATES | 1996 | 474800926.589179 UNITED STATES | 1995 | 464012837.42631 UNITED STATES | 1994 | 473002748.569221 UNITED STATES | 1993 | 470189312.520509 UNITED STATES | 1992 | 459569990.144074 VIETNAM | 1998 | 272739831.212103 VIETNAM | 1997 | 456233719.263165 VIETNAM | 1996 | 463110147.555322 VIETNAM | 1995 | 455211550.328876 VIETNAM | 1994 | 461115860.624812 VIETNAM | 1993 | 459501278.303727 VIETNAM | 1992 | 459254174.724046 (175 rows) COMMIT; COMMIT