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 '%puff%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261476509.869418 ALGERIA | 1997 | 453954268.074922 ALGERIA | 1996 | 454386380.272263 ALGERIA | 1995 | 445722701.933764 ALGERIA | 1994 | 451526920.929426 ALGERIA | 1993 | 451977744.599955 ALGERIA | 1992 | 455781211.540805 ARGENTINA | 1998 | 273517241.439239 ARGENTINA | 1997 | 469538558.137875 ARGENTINA | 1996 | 478059851.655103 ARGENTINA | 1995 | 471262329.816017 ARGENTINA | 1994 | 464219200.767674 ARGENTINA | 1993 | 471136457.52043 ARGENTINA | 1992 | 466740745.956921 BRAZIL | 1998 | 273074659.601736 BRAZIL | 1997 | 469541188.481666 BRAZIL | 1996 | 461633311.943533 BRAZIL | 1995 | 463754523.425883 BRAZIL | 1994 | 466570096.591826 BRAZIL | 1993 | 465517962.415856 BRAZIL | 1992 | 477824684.980077 CANADA | 1998 | 277967274.114543 CANADA | 1997 | 476370625.123472 CANADA | 1996 | 476641523.709467 CANADA | 1995 | 478857942.023695 CANADA | 1994 | 479328883.664764 CANADA | 1993 | 469741648.85929 CANADA | 1992 | 475755209.128889 CHINA | 1998 | 273288263.265104 CHINA | 1997 | 463747822.380861 CHINA | 1996 | 459803762.79157 CHINA | 1995 | 455413284.206115 CHINA | 1994 | 460894269.02624 CHINA | 1993 | 465122665.875441 CHINA | 1992 | 462365554.254475 EGYPT | 1998 | 265657938.352427 EGYPT | 1997 | 457165704.268111 EGYPT | 1996 | 464224444.249616 EGYPT | 1995 | 459179559.84045 EGYPT | 1994 | 461640255.738252 EGYPT | 1993 | 451427547.652898 EGYPT | 1992 | 456902967.811646 ETHIOPIA | 1998 | 268991845.418355 ETHIOPIA | 1997 | 464932493.459006 ETHIOPIA | 1996 | 462915922.795515 ETHIOPIA | 1995 | 469534623.513254 ETHIOPIA | 1994 | 466653809.370303 ETHIOPIA | 1993 | 467706371.759312 ETHIOPIA | 1992 | 471177391.293421 FRANCE | 1998 | 266802982.332901 FRANCE | 1997 | 449446182.174375 FRANCE | 1996 | 461233529.171081 FRANCE | 1995 | 461361593.146319 FRANCE | 1994 | 460276695.053922 FRANCE | 1993 | 460420763.408792 FRANCE | 1992 | 458053314.886294 GERMANY | 1998 | 281592386.904622 GERMANY | 1997 | 473811646.872792 GERMANY | 1996 | 476585938.445928 GERMANY | 1995 | 474812138.147894 GERMANY | 1994 | 482308801.029784 GERMANY | 1993 | 473724549.156009 GERMANY | 1992 | 469306829.627966 INDIA | 1998 | 269130800.049529 INDIA | 1997 | 474457075.174906 INDIA | 1996 | 475464827.070671 INDIA | 1995 | 469578252.070578 INDIA | 1994 | 475513985.814121 INDIA | 1993 | 471086172.188782 INDIA | 1992 | 481636758.205614 INDONESIA | 1998 | 270198005.423577 INDONESIA | 1997 | 461624618.04501 INDONESIA | 1996 | 470010646.630311 INDONESIA | 1995 | 464842241.124124 INDONESIA | 1994 | 472124804.224194 INDONESIA | 1993 | 464941917.771877 INDONESIA | 1992 | 471438237.987475 IRAN | 1998 | 276035410.26199 IRAN | 1997 | 461999634.741024 IRAN | 1996 | 461630200.207463 IRAN | 1995 | 461758097.420929 IRAN | 1994 | 462067426.895967 IRAN | 1993 | 454396375.45609 IRAN | 1992 | 459687722.116002 IRAQ | 1998 | 272545143.465963 IRAQ | 1997 | 471903106.242174 IRAQ | 1996 | 469170787.18924 IRAQ | 1995 | 471246539.126782 IRAQ | 1994 | 474398395.318267 IRAQ | 1993 | 469833231.206368 IRAQ | 1992 | 473786692.959528 JAPAN | 1998 | 273976741.654847 JAPAN | 1997 | 470702878.396341 JAPAN | 1996 | 467155881.257832 JAPAN | 1995 | 468653596.509391 JAPAN | 1994 | 471401677.437371 JAPAN | 1993 | 472467500.41866 JAPAN | 1992 | 462021634.545284 JORDAN | 1998 | 273760285.169727 JORDAN | 1997 | 463089258.95904 JORDAN | 1996 | 459506596.12398 JORDAN | 1995 | 461802840.567575 JORDAN | 1994 | 466381244.187075 JORDAN | 1993 | 464948806.571306 JORDAN | 1992 | 454184947.651446 KENYA | 1998 | 280279733.510628 KENYA | 1997 | 473138051.579088 KENYA | 1996 | 477639733.286906 KENYA | 1995 | 468249018.58515 KENYA | 1994 | 473942500.36205 KENYA | 1993 | 478605272.885941 KENYA | 1992 | 483004225.786426 MOROCCO | 1998 | 270020657.237347 MOROCCO | 1997 | 467231882.709927 MOROCCO | 1996 | 467000763.693898 MOROCCO | 1995 | 476595545.659221 MOROCCO | 1994 | 472625297.011441 MOROCCO | 1993 | 464839994.120095 MOROCCO | 1992 | 465226253.065952 MOZAMBIQUE | 1998 | 271482172.46295 MOZAMBIQUE | 1997 | 457900400.105849 MOZAMBIQUE | 1996 | 459935731.993622 MOZAMBIQUE | 1995 | 458259489.964715 MOZAMBIQUE | 1994 | 457844104.608549 MOZAMBIQUE | 1993 | 455208744.981102 MOZAMBIQUE | 1992 | 456049047.353273 PERU | 1998 | 276647795.629426 PERU | 1997 | 464925986.733841 PERU | 1996 | 476180825.714169 PERU | 1995 | 466186240.329081 PERU | 1994 | 466840477.788413 PERU | 1993 | 470814743.457136 PERU | 1992 | 460062227.77284 ROMANIA | 1998 | 271343829.091729 ROMANIA | 1997 | 457536540.550696 ROMANIA | 1996 | 464897973.264084 ROMANIA | 1995 | 458978854.182259 ROMANIA | 1994 | 458407820.717541 ROMANIA | 1993 | 459655064.51883 ROMANIA | 1992 | 469516161.346172 RUSSIA | 1998 | 276575942.752037 RUSSIA | 1997 | 475632075.02763 RUSSIA | 1996 | 469577828.995142 RUSSIA | 1995 | 472427578.608504 RUSSIA | 1994 | 476029423.002245 RUSSIA | 1993 | 473564940.046382 RUSSIA | 1992 | 475903534.509914 SAUDI ARABIA | 1998 | 278591794.736347 SAUDI ARABIA | 1997 | 471544237.943422 SAUDI ARABIA | 1996 | 479719180.403381 SAUDI ARABIA | 1995 | 473830549.139425 SAUDI ARABIA | 1994 | 472845437.7041 SAUDI ARABIA | 1993 | 476362357.311548 SAUDI ARABIA | 1992 | 475970640.110023 UNITED KINGDOM | 1998 | 272411393.238347 UNITED KINGDOM | 1997 | 459006739.280892 UNITED KINGDOM | 1996 | 465214157.627935 UNITED KINGDOM | 1995 | 468136573.222717 UNITED KINGDOM | 1994 | 456397344.810196 UNITED KINGDOM | 1993 | 461095043.099863 UNITED KINGDOM | 1992 | 463806221.141842 UNITED STATES | 1998 | 282130862.71929 UNITED STATES | 1997 | 468929416.078984 UNITED STATES | 1996 | 476860542.79777 UNITED STATES | 1995 | 479417021.40658 UNITED STATES | 1994 | 475271176.976139 UNITED STATES | 1993 | 475690348.998248 UNITED STATES | 1992 | 478338207.566021 VIETNAM | 1998 | 270476011.995947 VIETNAM | 1997 | 466885221.672718 VIETNAM | 1996 | 463293197.695839 VIETNAM | 1995 | 455038865.12135 VIETNAM | 1994 | 457853056.46736 VIETNAM | 1993 | 467669678.660489 VIETNAM | 1992 | 465655686.253023 (175 rows) COMMIT; COMMIT