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 '%tan%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267316509.385612 ALGERIA | 1997 | 461322910.412666 ALGERIA | 1996 | 458024008.840153 ALGERIA | 1995 | 461750825.913393 ALGERIA | 1994 | 462960279.587223 ALGERIA | 1993 | 455432029.220593 ALGERIA | 1992 | 459463418.736168 ARGENTINA | 1998 | 277044705.501782 ARGENTINA | 1997 | 472017647.011302 ARGENTINA | 1996 | 464874888.445102 ARGENTINA | 1995 | 465944458.300245 ARGENTINA | 1994 | 468623051.063479 ARGENTINA | 1993 | 462880020.002556 ARGENTINA | 1992 | 472980386.821185 BRAZIL | 1998 | 275551087.357896 BRAZIL | 1997 | 470623854.962852 BRAZIL | 1996 | 466417418.181988 BRAZIL | 1995 | 474347021.011097 BRAZIL | 1994 | 464083390.929399 BRAZIL | 1993 | 472749997.019302 BRAZIL | 1992 | 470395046.168002 CANADA | 1998 | 277959587.465376 CANADA | 1997 | 473477581.107629 CANADA | 1996 | 476649424.335053 CANADA | 1995 | 476880303.690323 CANADA | 1994 | 474771721.243898 CANADA | 1993 | 479262853.090343 CANADA | 1992 | 479123115.317246 CHINA | 1998 | 271107064.617896 CHINA | 1997 | 457956270.854625 CHINA | 1996 | 463707186.320382 CHINA | 1995 | 462405030.048247 CHINA | 1994 | 462039355.082734 CHINA | 1993 | 461138840.333837 CHINA | 1992 | 468745220.672664 EGYPT | 1998 | 277501226.968364 EGYPT | 1997 | 465710922.521689 EGYPT | 1996 | 467861075.609724 EGYPT | 1995 | 466426733.856922 EGYPT | 1994 | 460806702.753275 EGYPT | 1993 | 467585147.270359 EGYPT | 1992 | 471889011.918855 ETHIOPIA | 1998 | 269871665.56095 ETHIOPIA | 1997 | 455480657.662921 ETHIOPIA | 1996 | 457475449.749067 ETHIOPIA | 1995 | 458077927.311277 ETHIOPIA | 1994 | 461455281.670751 ETHIOPIA | 1993 | 455658100.943362 ETHIOPIA | 1992 | 457445629.487975 FRANCE | 1998 | 266745353.626577 FRANCE | 1997 | 449588331.510066 FRANCE | 1996 | 453074881.420324 FRANCE | 1995 | 457280355.54214 FRANCE | 1994 | 460552935.602835 FRANCE | 1993 | 460345244.314587 FRANCE | 1992 | 451906119.842193 GERMANY | 1998 | 277145421.900079 GERMANY | 1997 | 475567402.939609 GERMANY | 1996 | 477922391.517792 GERMANY | 1995 | 475537032.296445 GERMANY | 1994 | 477044084.548579 GERMANY | 1993 | 467525003.276272 GERMANY | 1992 | 470360402.204553 INDIA | 1998 | 277465710.368566 INDIA | 1997 | 468208236.675887 INDIA | 1996 | 474794191.034596 INDIA | 1995 | 473212694.438976 INDIA | 1994 | 473444806.193687 INDIA | 1993 | 474682549.923209 INDIA | 1992 | 477372737.618504 INDONESIA | 1998 | 274209653.38828 INDONESIA | 1997 | 463284995.70163 INDONESIA | 1996 | 463637984.277655 INDONESIA | 1995 | 463190290.580002 INDONESIA | 1994 | 463641862.74594 INDONESIA | 1993 | 464827097.394119 INDONESIA | 1992 | 460639125.998532 IRAN | 1998 | 272967489.819422 IRAN | 1997 | 475400145.04923 IRAN | 1996 | 472287067.963597 IRAN | 1995 | 471219640.309896 IRAN | 1994 | 476808107.137986 IRAN | 1993 | 472873813.459012 IRAN | 1992 | 477659344.623468 IRAQ | 1998 | 281119375.330897 IRAQ | 1997 | 473814517.767586 IRAQ | 1996 | 481454237.649038 IRAQ | 1995 | 471817219.990649 IRAQ | 1994 | 481309618.608571 IRAQ | 1993 | 472399321.35828 IRAQ | 1992 | 475751011.298703 JAPAN | 1998 | 278032681.660341 JAPAN | 1997 | 461964177.093269 JAPAN | 1996 | 458589796.54871 JAPAN | 1995 | 465504569.094834 JAPAN | 1994 | 462331750.442267 JAPAN | 1993 | 463081634.066013 JAPAN | 1992 | 466207353.058999 JORDAN | 1998 | 267861723.866039 JORDAN | 1997 | 461317489.523117 JORDAN | 1996 | 466442673.644449 JORDAN | 1995 | 457701783.197899 JORDAN | 1994 | 461990461.179702 JORDAN | 1993 | 451590285.843698 JORDAN | 1992 | 467513826.229304 KENYA | 1998 | 269699560.366837 KENYA | 1997 | 464136403.264517 KENYA | 1996 | 468046198.910708 KENYA | 1995 | 467026464.081087 KENYA | 1994 | 474881592.860929 KENYA | 1993 | 474292213.464398 KENYA | 1992 | 461798091.277218 MOROCCO | 1998 | 278635795.586546 MOROCCO | 1997 | 471932395.547868 MOROCCO | 1996 | 472377836.047916 MOROCCO | 1995 | 466683719.12071 MOROCCO | 1994 | 465046940.004978 MOROCCO | 1993 | 480086057.791868 MOROCCO | 1992 | 470972769.505923 MOZAMBIQUE | 1998 | 268732630.818465 MOZAMBIQUE | 1997 | 457482284.235642 MOZAMBIQUE | 1996 | 462338313.202101 MOZAMBIQUE | 1995 | 457426258.417996 MOZAMBIQUE | 1994 | 459354175.651201 MOZAMBIQUE | 1993 | 459549587.737584 MOZAMBIQUE | 1992 | 463175195.893444 PERU | 1998 | 273502896.78059 PERU | 1997 | 458310151.170014 PERU | 1996 | 458111798.310895 PERU | 1995 | 462928270.387913 PERU | 1994 | 457172707.307842 PERU | 1993 | 457499260.669208 PERU | 1992 | 454494125.20865 ROMANIA | 1998 | 272072528.608875 ROMANIA | 1997 | 467096111.383838 ROMANIA | 1996 | 466959902.630864 ROMANIA | 1995 | 470576947.117482 ROMANIA | 1994 | 458662237.7343 ROMANIA | 1993 | 465969213.304536 ROMANIA | 1992 | 473872831.896438 RUSSIA | 1998 | 281790899.210078 RUSSIA | 1997 | 483833892.280449 RUSSIA | 1996 | 478026914.405631 RUSSIA | 1995 | 475646681.943505 RUSSIA | 1994 | 475143381.370571 RUSSIA | 1993 | 473452275.70277 RUSSIA | 1992 | 470684363.577717 SAUDI ARABIA | 1998 | 276911062.95638 SAUDI ARABIA | 1997 | 471525533.639801 SAUDI ARABIA | 1996 | 467168757.373881 SAUDI ARABIA | 1995 | 464547217.066452 SAUDI ARABIA | 1994 | 469694383.040989 SAUDI ARABIA | 1993 | 464649197.283495 SAUDI ARABIA | 1992 | 465099194.377195 UNITED KINGDOM | 1998 | 267236353.372861 UNITED KINGDOM | 1997 | 459443503.212973 UNITED KINGDOM | 1996 | 461719407.809247 UNITED KINGDOM | 1995 | 460734544.729516 UNITED KINGDOM | 1994 | 457694133.924155 UNITED KINGDOM | 1993 | 459290845.860907 UNITED KINGDOM | 1992 | 461858794.151836 UNITED STATES | 1998 | 274849152.573129 UNITED STATES | 1997 | 477396244.903513 UNITED STATES | 1996 | 473833716.254326 UNITED STATES | 1995 | 471789746.700195 UNITED STATES | 1994 | 471657359.643129 UNITED STATES | 1993 | 475825129.810182 UNITED STATES | 1992 | 478635262.607842 VIETNAM | 1998 | 268868151.984586 VIETNAM | 1997 | 463968923.064162 VIETNAM | 1996 | 458575881.655722 VIETNAM | 1995 | 463672159.242389 VIETNAM | 1994 | 460173698.413214 VIETNAM | 1993 | 456052464.654315 VIETNAM | 1992 | 452441010.563163 (175 rows) COMMIT; COMMIT