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 '%orange%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271109761.175146 ALGERIA | 1997 | 461324843.669184 ALGERIA | 1996 | 463282055.317845 ALGERIA | 1995 | 464672872.67658 ALGERIA | 1994 | 456688397.034218 ALGERIA | 1993 | 460167960.469085 ALGERIA | 1992 | 459855227.560921 ARGENTINA | 1998 | 274155518.054667 ARGENTINA | 1997 | 464277833.379773 ARGENTINA | 1996 | 466572421.780019 ARGENTINA | 1995 | 464184930.37381 ARGENTINA | 1994 | 476915853.449299 ARGENTINA | 1993 | 469355921.279757 ARGENTINA | 1992 | 467624989.50683 BRAZIL | 1998 | 271878339.907538 BRAZIL | 1997 | 466246288.834249 BRAZIL | 1996 | 464716316.859501 BRAZIL | 1995 | 461039296.631193 BRAZIL | 1994 | 461938827.416965 BRAZIL | 1993 | 466771788.571037 BRAZIL | 1992 | 470501078.901574 CANADA | 1998 | 275116136.783642 CANADA | 1997 | 472775740.253818 CANADA | 1996 | 477646603.51514 CANADA | 1995 | 472718837.412793 CANADA | 1994 | 478999890.246846 CANADA | 1993 | 476474260.469974 CANADA | 1992 | 472917575.366432 CHINA | 1998 | 275292855.656273 CHINA | 1997 | 467114305.155084 CHINA | 1996 | 463914143.48045 CHINA | 1995 | 464656233.04711 CHINA | 1994 | 471297541.426453 CHINA | 1993 | 468379360.597163 CHINA | 1992 | 467565794.164784 EGYPT | 1998 | 266284044.81743 EGYPT | 1997 | 456737806.95007 EGYPT | 1996 | 456824028.587103 EGYPT | 1995 | 451425682.617569 EGYPT | 1994 | 449073663.440127 EGYPT | 1993 | 457123506.344818 EGYPT | 1992 | 460531834.199585 ETHIOPIA | 1998 | 269267987.53305 ETHIOPIA | 1997 | 451793908.332415 ETHIOPIA | 1996 | 455336373.54393 ETHIOPIA | 1995 | 455377121.370599 ETHIOPIA | 1994 | 455388702.90515 ETHIOPIA | 1993 | 457072609.800779 ETHIOPIA | 1992 | 455385827.932283 FRANCE | 1998 | 266358741.772304 FRANCE | 1997 | 460562531.658554 FRANCE | 1996 | 463913751.469849 FRANCE | 1995 | 467465223.234795 FRANCE | 1994 | 464514046.24511 FRANCE | 1993 | 464368772.686132 FRANCE | 1992 | 467524151.40085 GERMANY | 1998 | 283149645.148888 GERMANY | 1997 | 471165183.989123 GERMANY | 1996 | 473839394.02087 GERMANY | 1995 | 481141797.600735 GERMANY | 1994 | 469982149.056723 GERMANY | 1993 | 484711776.497302 GERMANY | 1992 | 476166696.811432 INDIA | 1998 | 273046641.163067 INDIA | 1997 | 471981401.295956 INDIA | 1996 | 479627902.023753 INDIA | 1995 | 461357318.478523 INDIA | 1994 | 467704592.90817 INDIA | 1993 | 467572768.254646 INDIA | 1992 | 465744811.243591 INDONESIA | 1998 | 269828288.622925 INDONESIA | 1997 | 460072448.810054 INDONESIA | 1996 | 467992417.458747 INDONESIA | 1995 | 449335464.504548 INDONESIA | 1994 | 460595631.26409 INDONESIA | 1993 | 463358160.825485 INDONESIA | 1992 | 463656074.476974 IRAN | 1998 | 275427508.044209 IRAN | 1997 | 463294182.374098 IRAN | 1996 | 468244035.341396 IRAN | 1995 | 468016123.024299 IRAN | 1994 | 462537750.734954 IRAN | 1993 | 469161683.212193 IRAN | 1992 | 474482255.219393 IRAQ | 1998 | 279061527.984546 IRAQ | 1997 | 477066438.683344 IRAQ | 1996 | 480809516.499501 IRAQ | 1995 | 475761227.669585 IRAQ | 1994 | 480977184.989012 IRAQ | 1993 | 470910393.728465 IRAQ | 1992 | 485452637.208696 JAPAN | 1998 | 268227988.014768 JAPAN | 1997 | 459720021.594089 JAPAN | 1996 | 462246624.136924 JAPAN | 1995 | 457937340.807406 JAPAN | 1994 | 458992153.307564 JAPAN | 1993 | 459341298.367484 JAPAN | 1992 | 461573238.592904 JORDAN | 1998 | 268829432.658758 JORDAN | 1997 | 456573803.784731 JORDAN | 1996 | 452058611.600272 JORDAN | 1995 | 459310290.707817 JORDAN | 1994 | 455521469.535391 JORDAN | 1993 | 454748467.389853 JORDAN | 1992 | 458863151.016277 KENYA | 1998 | 268314104.585061 KENYA | 1997 | 467057654.452008 KENYA | 1996 | 465708145.822905 KENYA | 1995 | 466930991.63556 KENYA | 1994 | 470847685.910557 KENYA | 1993 | 470485747.872658 KENYA | 1992 | 471634960.602847 MOROCCO | 1998 | 273425786.210293 MOROCCO | 1997 | 464536167.669818 MOROCCO | 1996 | 467980908.443689 MOROCCO | 1995 | 463345434.533057 MOROCCO | 1994 | 468426584.912049 MOROCCO | 1993 | 468965978.944777 MOROCCO | 1992 | 465168791.628865 MOZAMBIQUE | 1998 | 272026013.153491 MOZAMBIQUE | 1997 | 462211541.537263 MOZAMBIQUE | 1996 | 462152078.629427 MOZAMBIQUE | 1995 | 466611557.508608 MOZAMBIQUE | 1994 | 454862496.4042 MOZAMBIQUE | 1993 | 460979849.991833 MOZAMBIQUE | 1992 | 461674797.352855 PERU | 1998 | 268202591.514621 PERU | 1997 | 456207596.216833 PERU | 1996 | 458409691.442555 PERU | 1995 | 469959472.845013 PERU | 1994 | 455771848.098566 PERU | 1993 | 455562656.609291 PERU | 1992 | 462180697.27773 ROMANIA | 1998 | 273166632.967234 ROMANIA | 1997 | 477627595.094574 ROMANIA | 1996 | 476148259.453139 ROMANIA | 1995 | 474560526.44069 ROMANIA | 1994 | 473061402.467165 ROMANIA | 1993 | 478967299.190029 ROMANIA | 1992 | 471224124.923977 RUSSIA | 1998 | 279846093.886914 RUSSIA | 1997 | 471671299.95211 RUSSIA | 1996 | 480562018.118478 RUSSIA | 1995 | 483398817.414525 RUSSIA | 1994 | 479532751.81572 RUSSIA | 1993 | 470479934.864056 RUSSIA | 1992 | 476921140.032611 SAUDI ARABIA | 1998 | 277120904.841736 SAUDI ARABIA | 1997 | 464157603.739792 SAUDI ARABIA | 1996 | 472355353.25338 SAUDI ARABIA | 1995 | 470087186.434716 SAUDI ARABIA | 1994 | 471606878.192586 SAUDI ARABIA | 1993 | 471349316.22541 SAUDI ARABIA | 1992 | 474336508.837346 UNITED KINGDOM | 1998 | 268395158.943782 UNITED KINGDOM | 1997 | 457742551.737812 UNITED KINGDOM | 1996 | 456244434.405045 UNITED KINGDOM | 1995 | 456902495.564022 UNITED KINGDOM | 1994 | 452174955.299473 UNITED KINGDOM | 1993 | 452144848.105991 UNITED KINGDOM | 1992 | 453769750.687048 UNITED STATES | 1998 | 282560812.757018 UNITED STATES | 1997 | 471276253.908319 UNITED STATES | 1996 | 477898466.592441 UNITED STATES | 1995 | 471096087.901376 UNITED STATES | 1994 | 476825073.433497 UNITED STATES | 1993 | 480304165.904309 UNITED STATES | 1992 | 475907676.434409 VIETNAM | 1998 | 271431879.174746 VIETNAM | 1997 | 467213536.638574 VIETNAM | 1996 | 467608146.714399 VIETNAM | 1995 | 465572349.788677 VIETNAM | 1994 | 464875170.207329 VIETNAM | 1993 | 462169483.123199 VIETNAM | 1992 | 455620417.179436 (175 rows) COMMIT; COMMIT