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 | 269474442.362323 ALGERIA | 1997 | 456569968.441609 ALGERIA | 1996 | 461037272.883507 ALGERIA | 1995 | 471556260.523642 ALGERIA | 1994 | 463155530.489919 ALGERIA | 1993 | 460983328.538657 ALGERIA | 1992 | 464772623.981345 ARGENTINA | 1998 | 272920491.034249 ARGENTINA | 1997 | 471925446.47098 ARGENTINA | 1996 | 466499527.09202 ARGENTINA | 1995 | 462687602.079306 ARGENTINA | 1994 | 470338128.568964 ARGENTINA | 1993 | 464917059.264957 ARGENTINA | 1992 | 458852870.525197 BRAZIL | 1998 | 266679084.304315 BRAZIL | 1997 | 470936771.03373 BRAZIL | 1996 | 470464241.644231 BRAZIL | 1995 | 471472928.889227 BRAZIL | 1994 | 466580206.196088 BRAZIL | 1993 | 465695389.177013 BRAZIL | 1992 | 468676136.296539 CANADA | 1998 | 281022838.424554 CANADA | 1997 | 475215502.214012 CANADA | 1996 | 475906231.833725 CANADA | 1995 | 477588263.945641 CANADA | 1994 | 475219182.448734 CANADA | 1993 | 476843886.015653 CANADA | 1992 | 475994628.972169 CHINA | 1998 | 274233409.29444 CHINA | 1997 | 473212186.822654 CHINA | 1996 | 468810017.208919 CHINA | 1995 | 465877186.618459 CHINA | 1994 | 468207689.298528 CHINA | 1993 | 467776335.105952 CHINA | 1992 | 468395459.32422 EGYPT | 1998 | 273707052.959335 EGYPT | 1997 | 459987433.966634 EGYPT | 1996 | 473819448.498179 EGYPT | 1995 | 460785501.008325 EGYPT | 1994 | 462796689.697586 EGYPT | 1993 | 468740516.443295 EGYPT | 1992 | 463136590.227998 ETHIOPIA | 1998 | 265962127.662054 ETHIOPIA | 1997 | 465799604.619606 ETHIOPIA | 1996 | 457839238.896006 ETHIOPIA | 1995 | 460546888.129307 ETHIOPIA | 1994 | 462755024.073901 ETHIOPIA | 1993 | 460411320.018143 ETHIOPIA | 1992 | 466467085.957366 FRANCE | 1998 | 269340617.827055 FRANCE | 1997 | 466184507.810154 FRANCE | 1996 | 461227541.322764 FRANCE | 1995 | 459066372.934323 FRANCE | 1994 | 459158731.412993 FRANCE | 1993 | 458665206.801708 FRANCE | 1992 | 457969464.232126 GERMANY | 1998 | 271986615.78202 GERMANY | 1997 | 475694254.658603 GERMANY | 1996 | 472250205.672065 GERMANY | 1995 | 475736136.335719 GERMANY | 1994 | 471322396.719736 GERMANY | 1993 | 478645515.841066 GERMANY | 1992 | 470465388.527365 INDIA | 1998 | 275843657.221438 INDIA | 1997 | 477049532.375923 INDIA | 1996 | 483198271.903017 INDIA | 1995 | 477246303.430053 INDIA | 1994 | 478446803.802912 INDIA | 1993 | 483409565.250883 INDIA | 1992 | 476743127.076595 INDONESIA | 1998 | 276312920.308965 INDONESIA | 1997 | 464941225.97798 INDONESIA | 1996 | 461760856.330601 INDONESIA | 1995 | 450337583.259752 INDONESIA | 1994 | 459315131.780603 INDONESIA | 1993 | 471666009.497559 INDONESIA | 1992 | 461489006.323894 IRAN | 1998 | 268489493.763222 IRAN | 1997 | 468664686.150134 IRAN | 1996 | 468687261.784244 IRAN | 1995 | 472672623.037441 IRAN | 1994 | 464081141.716382 IRAN | 1993 | 470898601.064979 IRAN | 1992 | 476874147.473929 IRAQ | 1998 | 280307872.005757 IRAQ | 1997 | 471919015.217735 IRAQ | 1996 | 467984223.603207 IRAQ | 1995 | 472810076.860056 IRAQ | 1994 | 474733317.16268 IRAQ | 1993 | 476253306.762219 IRAQ | 1992 | 477667138.466182 JAPAN | 1998 | 270096327.896727 JAPAN | 1997 | 465090744.36542 JAPAN | 1996 | 457773763.167208 JAPAN | 1995 | 463510325.586652 JAPAN | 1994 | 458530076.573468 JAPAN | 1993 | 465383586.8571 JAPAN | 1992 | 473601778.005694 JORDAN | 1998 | 267312533.87418 JORDAN | 1997 | 465720773.210683 JORDAN | 1996 | 460349068.898504 JORDAN | 1995 | 467042011.92481 JORDAN | 1994 | 465807930.05313 JORDAN | 1993 | 465575981.818929 JORDAN | 1992 | 461096949.148822 KENYA | 1998 | 283019775.740168 KENYA | 1997 | 474397952.291766 KENYA | 1996 | 481645720.330925 KENYA | 1995 | 476762563.817553 KENYA | 1994 | 478373527.742442 KENYA | 1993 | 484398980.860632 KENYA | 1992 | 476612455.793011 MOROCCO | 1998 | 277227243.030768 MOROCCO | 1997 | 475243068.634693 MOROCCO | 1996 | 481572073.979783 MOROCCO | 1995 | 470788139.601841 MOROCCO | 1994 | 479136091.593368 MOROCCO | 1993 | 480468874.773729 MOROCCO | 1992 | 476226495.324251 MOZAMBIQUE | 1998 | 268293641.071803 MOZAMBIQUE | 1997 | 468475090.997385 MOZAMBIQUE | 1996 | 470673377.95722 MOZAMBIQUE | 1995 | 464739158.479346 MOZAMBIQUE | 1994 | 458687960.00129 MOZAMBIQUE | 1993 | 466981929.179991 MOZAMBIQUE | 1992 | 459375638.077784 PERU | 1998 | 275912486.249073 PERU | 1997 | 466258599.327371 PERU | 1996 | 474927017.19871 PERU | 1995 | 464712089.422875 PERU | 1994 | 475187321.885419 PERU | 1993 | 477628306.516034 PERU | 1992 | 476896768.159641 ROMANIA | 1998 | 271827602.822661 ROMANIA | 1997 | 470860558.543873 ROMANIA | 1996 | 475413840.318383 ROMANIA | 1995 | 472839351.209823 ROMANIA | 1994 | 467056375.209558 ROMANIA | 1993 | 462538725.171053 ROMANIA | 1992 | 470818328.723816 RUSSIA | 1998 | 276780450.461958 RUSSIA | 1997 | 474967268.119765 RUSSIA | 1996 | 475354311.211325 RUSSIA | 1995 | 468023698.099282 RUSSIA | 1994 | 476222865.89327 RUSSIA | 1993 | 469310824.737475 RUSSIA | 1992 | 468797075.048374 SAUDI ARABIA | 1998 | 274457249.809655 SAUDI ARABIA | 1997 | 463297448.282787 SAUDI ARABIA | 1996 | 468606744.070628 SAUDI ARABIA | 1995 | 470172054.230652 SAUDI ARABIA | 1994 | 471839959.212297 SAUDI ARABIA | 1993 | 474334222.467686 SAUDI ARABIA | 1992 | 467287813.902173 UNITED KINGDOM | 1998 | 268952970.428992 UNITED KINGDOM | 1997 | 454870533.654081 UNITED KINGDOM | 1996 | 461287122.821458 UNITED KINGDOM | 1995 | 459582607.670753 UNITED KINGDOM | 1994 | 463220977.347478 UNITED KINGDOM | 1993 | 463850641.154452 UNITED KINGDOM | 1992 | 467632181.245559 UNITED STATES | 1998 | 281395365.709849 UNITED STATES | 1997 | 479693026.421147 UNITED STATES | 1996 | 473847279.127409 UNITED STATES | 1995 | 477338741.67493 UNITED STATES | 1994 | 475859033.6875 UNITED STATES | 1993 | 475217700.446724 UNITED STATES | 1992 | 485243236.676959 VIETNAM | 1998 | 271571947.805671 VIETNAM | 1997 | 465767485.368747 VIETNAM | 1996 | 470817392.073375 VIETNAM | 1995 | 462433733.838511 VIETNAM | 1994 | 469150234.923726 VIETNAM | 1993 | 466990504.5107 VIETNAM | 1992 | 462229594.460558 (175 rows) COMMIT; COMMIT