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 '%almond%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268324975.993076 ALGERIA | 1997 | 453495213.601455 ALGERIA | 1996 | 457296469.823659 ALGERIA | 1995 | 459760931.632839 ALGERIA | 1994 | 450062259.109905 ALGERIA | 1993 | 459999449.367055 ALGERIA | 1992 | 452137844.81548 ARGENTINA | 1998 | 275255577.234866 ARGENTINA | 1997 | 471712477.134184 ARGENTINA | 1996 | 473656096.714705 ARGENTINA | 1995 | 466749242.536783 ARGENTINA | 1994 | 472628244.574154 ARGENTINA | 1993 | 476589605.744315 ARGENTINA | 1992 | 474087292.218906 BRAZIL | 1998 | 273325395.442216 BRAZIL | 1997 | 467236523.655332 BRAZIL | 1996 | 451862020.232258 BRAZIL | 1995 | 461820781.988846 BRAZIL | 1994 | 461619924.811075 BRAZIL | 1993 | 458596268.77507 BRAZIL | 1992 | 463591491.599991 CANADA | 1998 | 285500566.370874 CANADA | 1997 | 484933905.22529 CANADA | 1996 | 474205849.084266 CANADA | 1995 | 469429625.466105 CANADA | 1994 | 474159530.500206 CANADA | 1993 | 475655404.290944 CANADA | 1992 | 474425867.580222 CHINA | 1998 | 273763782.853756 CHINA | 1997 | 450141523.1672 CHINA | 1996 | 454630963.683786 CHINA | 1995 | 463479378.787518 CHINA | 1994 | 459196335.651446 CHINA | 1993 | 464037724.256767 CHINA | 1992 | 463806147.083726 EGYPT | 1998 | 271873858.996562 EGYPT | 1997 | 464242793.550021 EGYPT | 1996 | 466326529.403836 EGYPT | 1995 | 469904802.817527 EGYPT | 1994 | 467312462.006523 EGYPT | 1993 | 459341997.298534 EGYPT | 1992 | 463083084.480889 ETHIOPIA | 1998 | 267150641.711341 ETHIOPIA | 1997 | 457932838.606642 ETHIOPIA | 1996 | 455978114.203701 ETHIOPIA | 1995 | 454792289.188567 ETHIOPIA | 1994 | 459962111.211458 ETHIOPIA | 1993 | 463469761.797633 ETHIOPIA | 1992 | 459643057.624414 FRANCE | 1998 | 270340413.794906 FRANCE | 1997 | 452688242.367194 FRANCE | 1996 | 458844971.798991 FRANCE | 1995 | 453897333.955324 FRANCE | 1994 | 455106201.086189 FRANCE | 1993 | 451083149.779317 FRANCE | 1992 | 453901671.437322 GERMANY | 1998 | 279271831.515568 GERMANY | 1997 | 494769101.675515 GERMANY | 1996 | 484356154.071808 GERMANY | 1995 | 481842484.234745 GERMANY | 1994 | 481453043.364935 GERMANY | 1993 | 482613858.443647 GERMANY | 1992 | 481746714.261249 INDIA | 1998 | 272230075.312934 INDIA | 1997 | 473275597.724349 INDIA | 1996 | 473375232.290657 INDIA | 1995 | 465366673.181852 INDIA | 1994 | 473336798.087855 INDIA | 1993 | 473368223.231772 INDIA | 1992 | 466857850.340203 INDONESIA | 1998 | 266328933.552434 INDONESIA | 1997 | 462109695.956833 INDONESIA | 1996 | 459232793.226365 INDONESIA | 1995 | 463893074.021998 INDONESIA | 1994 | 456657384.006921 INDONESIA | 1993 | 463748256.95015 INDONESIA | 1992 | 469846556.032433 IRAN | 1998 | 270601535.754746 IRAN | 1997 | 468761741.668508 IRAN | 1996 | 467805988.297938 IRAN | 1995 | 467978582.124897 IRAN | 1994 | 469212329.015417 IRAN | 1993 | 466215000.562908 IRAN | 1992 | 469179713.137045 IRAQ | 1998 | 274742251.448749 IRAQ | 1997 | 479303350.792079 IRAQ | 1996 | 470477665.948694 IRAQ | 1995 | 472909632.69849 IRAQ | 1994 | 475698092.93201 IRAQ | 1993 | 477372121.695279 IRAQ | 1992 | 472375986.046571 JAPAN | 1998 | 269843234.803333 JAPAN | 1997 | 468855799.392841 JAPAN | 1996 | 462477070.599337 JAPAN | 1995 | 466495407.160272 JAPAN | 1994 | 464490111.927956 JAPAN | 1993 | 462529624.175195 JAPAN | 1992 | 465058179.702092 JORDAN | 1998 | 274096588.169166 JORDAN | 1997 | 463351547.667353 JORDAN | 1996 | 461267327.540357 JORDAN | 1995 | 465766419.569497 JORDAN | 1994 | 463055372.022366 JORDAN | 1993 | 466234527.606608 JORDAN | 1992 | 453181120.286114 KENYA | 1998 | 271093960.742458 KENYA | 1997 | 466146833.850914 KENYA | 1996 | 470974291.855775 KENYA | 1995 | 462727202.013991 KENYA | 1994 | 464544732.051311 KENYA | 1993 | 463895199.74159 KENYA | 1992 | 472462716.099222 MOROCCO | 1998 | 271353344.979298 MOROCCO | 1997 | 471734427.389876 MOROCCO | 1996 | 467404162.045812 MOROCCO | 1995 | 469451244.509092 MOROCCO | 1994 | 460954389.000431 MOROCCO | 1993 | 466426326.070258 MOROCCO | 1992 | 472278481.616103 MOZAMBIQUE | 1998 | 265309559.734556 MOZAMBIQUE | 1997 | 457528470.903012 MOZAMBIQUE | 1996 | 448236184.44521 MOZAMBIQUE | 1995 | 464943117.608809 MOZAMBIQUE | 1994 | 455019136.774723 MOZAMBIQUE | 1993 | 455674521.881796 MOZAMBIQUE | 1992 | 452873189.231798 PERU | 1998 | 280630546.933726 PERU | 1997 | 463539760.794979 PERU | 1996 | 474716939.465149 PERU | 1995 | 476642064.141188 PERU | 1994 | 465290100.53175 PERU | 1993 | 468359300.162289 PERU | 1992 | 484390116.587758 ROMANIA | 1998 | 272994014.623617 ROMANIA | 1997 | 468674007.90942 ROMANIA | 1996 | 471745349.497957 ROMANIA | 1995 | 460559601.030779 ROMANIA | 1994 | 468983094.285257 ROMANIA | 1993 | 470313728.879212 ROMANIA | 1992 | 470737672.886151 RUSSIA | 1998 | 271122237.136038 RUSSIA | 1997 | 465794525.525832 RUSSIA | 1996 | 466550887.842219 RUSSIA | 1995 | 463515083.499111 RUSSIA | 1994 | 466379879.227749 RUSSIA | 1993 | 465112247.12408 RUSSIA | 1992 | 468474336.620752 SAUDI ARABIA | 1998 | 272120745.083318 SAUDI ARABIA | 1997 | 472566000.354894 SAUDI ARABIA | 1996 | 467636804.220473 SAUDI ARABIA | 1995 | 472141233.149315 SAUDI ARABIA | 1994 | 474351868.427815 SAUDI ARABIA | 1993 | 477765306.407326 SAUDI ARABIA | 1992 | 474208455.000759 UNITED KINGDOM | 1998 | 272955693.749204 UNITED KINGDOM | 1997 | 462677642.071057 UNITED KINGDOM | 1996 | 462292351.683371 UNITED KINGDOM | 1995 | 458121639.079648 UNITED KINGDOM | 1994 | 461177625.314068 UNITED KINGDOM | 1993 | 458275222.979631 UNITED KINGDOM | 1992 | 468885969.291995 UNITED STATES | 1998 | 275019896.840322 UNITED STATES | 1997 | 481012993.938812 UNITED STATES | 1996 | 481426880.235791 UNITED STATES | 1995 | 475864889.128989 UNITED STATES | 1994 | 480649303.365223 UNITED STATES | 1993 | 484068832.993457 UNITED STATES | 1992 | 480789929.607985 VIETNAM | 1998 | 267930972.512983 VIETNAM | 1997 | 460087938.560431 VIETNAM | 1996 | 465003638.407933 VIETNAM | 1995 | 461437876.678308 VIETNAM | 1994 | 462372520.331811 VIETNAM | 1993 | 459536184.178473 VIETNAM | 1992 | 467022847.258377 (175 rows) COMMIT; COMMIT