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 '%orchid%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269025525.930693 ALGERIA | 1997 | 455842672.855591 ALGERIA | 1996 | 460895032.138199 ALGERIA | 1995 | 471133649.91101 ALGERIA | 1994 | 462371776.519263 ALGERIA | 1993 | 460790553.828865 ALGERIA | 1992 | 464434058.261715 ARGENTINA | 1998 | 272517865.375689 ARGENTINA | 1997 | 471694834.372041 ARGENTINA | 1996 | 466132679.043659 ARGENTINA | 1995 | 462523930.020371 ARGENTINA | 1994 | 470144029.84299 ARGENTINA | 1993 | 464051256.389242 ARGENTINA | 1992 | 458352679.134112 BRAZIL | 1998 | 266528818.51979 BRAZIL | 1997 | 470874369.261647 BRAZIL | 1996 | 469133038.524656 BRAZIL | 1995 | 471052744.327818 BRAZIL | 1994 | 466107727.432572 BRAZIL | 1993 | 465236157.107541 BRAZIL | 1992 | 468363604.003133 CANADA | 1998 | 281035537.206069 CANADA | 1997 | 475056487.051775 CANADA | 1996 | 475314182.429457 CANADA | 1995 | 477188051.648789 CANADA | 1994 | 474483362.495384 CANADA | 1993 | 476066317.286165 CANADA | 1992 | 475580124.939041 CHINA | 1998 | 273913323.99033 CHINA | 1997 | 472421013.04933 CHINA | 1996 | 467993666.964428 CHINA | 1995 | 465038655.502725 CHINA | 1994 | 467644328.063683 CHINA | 1993 | 467230455.820698 CHINA | 1992 | 467973443.790574 EGYPT | 1998 | 273456617.832709 EGYPT | 1997 | 459475139.38979 EGYPT | 1996 | 472922247.587077 EGYPT | 1995 | 460110341.569328 EGYPT | 1994 | 462499188.683718 EGYPT | 1993 | 468035489.164666 EGYPT | 1992 | 462778457.19229 ETHIOPIA | 1998 | 265795767.230345 ETHIOPIA | 1997 | 465168700.250212 ETHIOPIA | 1996 | 457818437.553236 ETHIOPIA | 1995 | 460166516.652566 ETHIOPIA | 1994 | 462493442.982904 ETHIOPIA | 1993 | 460509218.370599 ETHIOPIA | 1992 | 466179538.813328 FRANCE | 1998 | 269375283.360842 FRANCE | 1997 | 466203058.650435 FRANCE | 1996 | 461036195.854815 FRANCE | 1995 | 458495111.399311 FRANCE | 1994 | 458405102.499177 FRANCE | 1993 | 458259378.403136 FRANCE | 1992 | 457430086.646993 GERMANY | 1998 | 271731100.916508 GERMANY | 1997 | 475650243.605565 GERMANY | 1996 | 471973949.02865 GERMANY | 1995 | 475430608.189062 GERMANY | 1994 | 470939431.842193 GERMANY | 1993 | 477835228.119591 GERMANY | 1992 | 470218907.91333 INDIA | 1998 | 275463931.541944 INDIA | 1997 | 476504307.719357 INDIA | 1996 | 482543358.669656 INDIA | 1995 | 476793180.665391 INDIA | 1994 | 477766737.604329 INDIA | 1993 | 482900805.257967 INDIA | 1992 | 476557779.694556 INDONESIA | 1998 | 276015722.771078 INDONESIA | 1997 | 464428626.30883 INDONESIA | 1996 | 461078796.501208 INDONESIA | 1995 | 450087524.457832 INDONESIA | 1994 | 459225450.554257 INDONESIA | 1993 | 471128490.569358 INDONESIA | 1992 | 461174534.366845 IRAN | 1998 | 268035532.537254 IRAN | 1997 | 468028548.851029 IRAN | 1996 | 468125584.676162 IRAN | 1995 | 472025748.167262 IRAN | 1994 | 463578682.180528 IRAN | 1993 | 470626807.873376 IRAN | 1992 | 476703077.880438 IRAQ | 1998 | 280150343.665372 IRAQ | 1997 | 471375071.301078 IRAQ | 1996 | 467586042.194224 IRAQ | 1995 | 472302337.899147 IRAQ | 1994 | 474466722.500403 IRAQ | 1993 | 475875426.86928 IRAQ | 1992 | 477349916.522021 JAPAN | 1998 | 269697841.13535 JAPAN | 1997 | 464351528.331068 JAPAN | 1996 | 457523755.995957 JAPAN | 1995 | 463122078.09832 JAPAN | 1994 | 458109690.216746 JAPAN | 1993 | 464805052.967818 JAPAN | 1992 | 472725523.23362 JORDAN | 1998 | 267097417.782843 JORDAN | 1997 | 465437321.959493 JORDAN | 1996 | 459698314.161891 JORDAN | 1995 | 466963781.782203 JORDAN | 1994 | 465034055.356241 JORDAN | 1993 | 464672204.459046 JORDAN | 1992 | 460502028.81743 KENYA | 1998 | 282909433.026041 KENYA | 1997 | 473465413.22368 KENYA | 1996 | 481003260.078393 KENYA | 1995 | 476484187.316927 KENYA | 1994 | 477676155.594336 KENYA | 1993 | 484103974.808288 KENYA | 1992 | 476071168.96924 MOROCCO | 1998 | 277306106.516165 MOROCCO | 1997 | 474471701.270796 MOROCCO | 1996 | 480987711.857068 MOROCCO | 1995 | 470625515.740894 MOROCCO | 1994 | 478591077.512878 MOROCCO | 1993 | 479989231.196233 MOROCCO | 1992 | 475771464.140332 MOZAMBIQUE | 1998 | 268206736.810621 MOZAMBIQUE | 1997 | 467853827.182528 MOZAMBIQUE | 1996 | 470189292.034764 MOZAMBIQUE | 1995 | 464385502.598183 MOZAMBIQUE | 1994 | 458384211.818951 MOZAMBIQUE | 1993 | 466304375.852688 MOZAMBIQUE | 1992 | 458715688.654001 PERU | 1998 | 275812217.485686 PERU | 1997 | 465902382.602719 PERU | 1996 | 474638307.322088 PERU | 1995 | 464143029.416353 PERU | 1994 | 474737606.66193 PERU | 1993 | 477296243.688225 PERU | 1992 | 475590405.889252 ROMANIA | 1998 | 271421541.018627 ROMANIA | 1997 | 470395265.595527 ROMANIA | 1996 | 475046818.6254 ROMANIA | 1995 | 472289887.699929 ROMANIA | 1994 | 466591901.600456 ROMANIA | 1993 | 462046194.10452 ROMANIA | 1992 | 470446687.697395 RUSSIA | 1998 | 276225189.470642 RUSSIA | 1997 | 474348741.628639 RUSSIA | 1996 | 475209806.653984 RUSSIA | 1995 | 467696509.680115 RUSSIA | 1994 | 475906557.245541 RUSSIA | 1993 | 469027775.303517 RUSSIA | 1992 | 468156793.855973 SAUDI ARABIA | 1998 | 274153670.630849 SAUDI ARABIA | 1997 | 462515618.802479 SAUDI ARABIA | 1996 | 468390138.186279 SAUDI ARABIA | 1995 | 469551857.86713 SAUDI ARABIA | 1994 | 471714018.249724 SAUDI ARABIA | 1993 | 473904381.078043 SAUDI ARABIA | 1992 | 466571524.745451 UNITED KINGDOM | 1998 | 268671791.721282 UNITED KINGDOM | 1997 | 454377471.281032 UNITED KINGDOM | 1996 | 461003511.85492 UNITED KINGDOM | 1995 | 459140245.580685 UNITED KINGDOM | 1994 | 462264877.150747 UNITED KINGDOM | 1993 | 463200808.032948 UNITED KINGDOM | 1992 | 466734932.152273 UNITED STATES | 1998 | 280894840.388834 UNITED STATES | 1997 | 479146898.200834 UNITED STATES | 1996 | 473136180.044294 UNITED STATES | 1995 | 476790213.767856 UNITED STATES | 1994 | 475729404.417837 UNITED STATES | 1993 | 474967271.54387 UNITED STATES | 1992 | 485246824.424321 VIETNAM | 1998 | 271232679.198299 VIETNAM | 1997 | 465107856.292769 VIETNAM | 1996 | 470650595.429932 VIETNAM | 1995 | 462152592.405012 VIETNAM | 1994 | 468619136.464353 VIETNAM | 1993 | 466402493.134889 VIETNAM | 1992 | 461430248.306913 (175 rows) COMMIT; COMMIT