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 '%antique%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266641484.731481 ALGERIA | 1997 | 447897322.52854 ALGERIA | 1996 | 453319074.258377 ALGERIA | 1995 | 456983391.429411 ALGERIA | 1994 | 444417711.041916 ALGERIA | 1993 | 452350277.140014 ALGERIA | 1992 | 451757452.419135 ARGENTINA | 1998 | 279240652.733601 ARGENTINA | 1997 | 473600105.152045 ARGENTINA | 1996 | 469044621.823649 ARGENTINA | 1995 | 474327079.573092 ARGENTINA | 1994 | 471993671.523983 ARGENTINA | 1993 | 467949223.6696 ARGENTINA | 1992 | 472570988.384484 BRAZIL | 1998 | 270431510.757806 BRAZIL | 1997 | 464536475.220632 BRAZIL | 1996 | 459332354.009157 BRAZIL | 1995 | 464810972.683226 BRAZIL | 1994 | 460834420.386223 BRAZIL | 1993 | 460169065.701438 BRAZIL | 1992 | 459484304.668512 CANADA | 1998 | 277275550.436823 CANADA | 1997 | 474622638.31228 CANADA | 1996 | 469290406.086886 CANADA | 1995 | 468578385.151886 CANADA | 1994 | 480686584.576556 CANADA | 1993 | 479351467.725298 CANADA | 1992 | 465333064.717357 CHINA | 1998 | 269098951.492392 CHINA | 1997 | 456991355.903901 CHINA | 1996 | 461349303.798604 CHINA | 1995 | 458472383.133266 CHINA | 1994 | 455831528.03981 CHINA | 1993 | 469996434.891924 CHINA | 1992 | 459169468.950846 EGYPT | 1998 | 275145796.719337 EGYPT | 1997 | 470263509.121282 EGYPT | 1996 | 472074601.785168 EGYPT | 1995 | 471305840.22784 EGYPT | 1994 | 468384534.498223 EGYPT | 1993 | 469045543.64926 EGYPT | 1992 | 459897880.091551 ETHIOPIA | 1998 | 264944415.461329 ETHIOPIA | 1997 | 456072143.401505 ETHIOPIA | 1996 | 460410014.214261 ETHIOPIA | 1995 | 456677556.673995 ETHIOPIA | 1994 | 464894002.298934 ETHIOPIA | 1993 | 461880920.933355 ETHIOPIA | 1992 | 456172877.127433 FRANCE | 1998 | 266899071.042718 FRANCE | 1997 | 456712125.574232 FRANCE | 1996 | 456062409.326448 FRANCE | 1995 | 453494930.60779 FRANCE | 1994 | 454527910.033763 FRANCE | 1993 | 453934259.507686 FRANCE | 1992 | 455906741.492201 GERMANY | 1998 | 285152167.251479 GERMANY | 1997 | 472852471.344696 GERMANY | 1996 | 474201589.472286 GERMANY | 1995 | 482503465.051635 GERMANY | 1994 | 471103881.384557 GERMANY | 1993 | 470195946.37033 GERMANY | 1992 | 468804248.277848 INDIA | 1998 | 272042715.032935 INDIA | 1997 | 467044917.699862 INDIA | 1996 | 481087187.084322 INDIA | 1995 | 472635240.675305 INDIA | 1994 | 472099155.886764 INDIA | 1993 | 469964006.119648 INDIA | 1992 | 472406528.592578 INDONESIA | 1998 | 273210228.074396 INDONESIA | 1997 | 463693639.705689 INDONESIA | 1996 | 463470709.203892 INDONESIA | 1995 | 462889384.087543 INDONESIA | 1994 | 469666380.444442 INDONESIA | 1993 | 464584804.935122 INDONESIA | 1992 | 469514036.514278 IRAN | 1998 | 275834560.66634 IRAN | 1997 | 464578363.199123 IRAN | 1996 | 477194562.931034 IRAN | 1995 | 456832061.813879 IRAN | 1994 | 463682023.455475 IRAN | 1993 | 469798195.378717 IRAN | 1992 | 470914453.955271 IRAQ | 1998 | 267490864.062959 IRAQ | 1997 | 456542432.806259 IRAQ | 1996 | 465372472.861353 IRAQ | 1995 | 451931857.097799 IRAQ | 1994 | 454545298.910958 IRAQ | 1993 | 457438132.904846 IRAQ | 1992 | 455686075.382708 JAPAN | 1998 | 272188045.140569 JAPAN | 1997 | 465476299.00176 JAPAN | 1996 | 461752157.685438 JAPAN | 1995 | 468173403.458097 JAPAN | 1994 | 464914203.284927 JAPAN | 1993 | 465204689.305363 JAPAN | 1992 | 459799361.103249 JORDAN | 1998 | 262820283.870077 JORDAN | 1997 | 451067898.113383 JORDAN | 1996 | 449203108.693631 JORDAN | 1995 | 454189824.77784 JORDAN | 1994 | 449110128.738466 JORDAN | 1993 | 454711817.873548 JORDAN | 1992 | 447456470.33976 KENYA | 1998 | 275576254.734954 KENYA | 1997 | 474922979.725871 KENYA | 1996 | 479543230.076464 KENYA | 1995 | 466198862.435832 KENYA | 1994 | 478339095.907024 KENYA | 1993 | 469950258.618532 KENYA | 1992 | 475979603.265931 MOROCCO | 1998 | 270354311.326947 MOROCCO | 1997 | 472351131.43162 MOROCCO | 1996 | 468966556.450384 MOROCCO | 1995 | 463977182.441093 MOROCCO | 1994 | 464987112.932724 MOROCCO | 1993 | 464238900.82649 MOROCCO | 1992 | 474045008.152809 MOZAMBIQUE | 1998 | 268528846.001849 MOZAMBIQUE | 1997 | 459259885.235309 MOZAMBIQUE | 1996 | 458256714.554938 MOZAMBIQUE | 1995 | 458997746.289405 MOZAMBIQUE | 1994 | 452806709.045017 MOZAMBIQUE | 1993 | 457135831.258573 MOZAMBIQUE | 1992 | 457315732.262801 PERU | 1998 | 269689448.43845 PERU | 1997 | 451117944.612236 PERU | 1996 | 456566339.324828 PERU | 1995 | 453939572.537459 PERU | 1994 | 451543395.67455 PERU | 1993 | 455016023.297187 PERU | 1992 | 459962763.771446 ROMANIA | 1998 | 271919736.367306 ROMANIA | 1997 | 469686913.956219 ROMANIA | 1996 | 467351974.308379 ROMANIA | 1995 | 467583341.600212 ROMANIA | 1994 | 463367759.407522 ROMANIA | 1993 | 463788780.038319 ROMANIA | 1992 | 460162915.419112 RUSSIA | 1998 | 272896366.032967 RUSSIA | 1997 | 468675252.498653 RUSSIA | 1996 | 474833104.342775 RUSSIA | 1995 | 465792274.788017 RUSSIA | 1994 | 462672107.732816 RUSSIA | 1993 | 466502800.794109 RUSSIA | 1992 | 468033336.4725 SAUDI ARABIA | 1998 | 272562030.830739 SAUDI ARABIA | 1997 | 465030639.743864 SAUDI ARABIA | 1996 | 471627963.647719 SAUDI ARABIA | 1995 | 471466392.97391 SAUDI ARABIA | 1994 | 470931606.020122 SAUDI ARABIA | 1993 | 467570152.947672 SAUDI ARABIA | 1992 | 471221995.870813 UNITED KINGDOM | 1998 | 275046302.845474 UNITED KINGDOM | 1997 | 461282295.358865 UNITED KINGDOM | 1996 | 459812179.44568 UNITED KINGDOM | 1995 | 460112988.739987 UNITED KINGDOM | 1994 | 454692814.60911 UNITED KINGDOM | 1993 | 463708602.159254 UNITED KINGDOM | 1992 | 461202215.201681 UNITED STATES | 1998 | 266848700.742651 UNITED STATES | 1997 | 461833809.297231 UNITED STATES | 1996 | 467164880.131594 UNITED STATES | 1995 | 471146006.855156 UNITED STATES | 1994 | 470284723.809053 UNITED STATES | 1993 | 464103131.815829 UNITED STATES | 1992 | 457189456.908241 VIETNAM | 1998 | 271129916.757664 VIETNAM | 1997 | 463032703.001208 VIETNAM | 1996 | 461779625.532369 VIETNAM | 1995 | 460456726.304325 VIETNAM | 1994 | 467733160.058794 VIETNAM | 1993 | 465851727.030343 VIETNAM | 1992 | 464517333.516813 (175 rows) COMMIT; COMMIT