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 '%orange%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271181375.806778 ALGERIA | 1997 | 460845080.986555 ALGERIA | 1996 | 463243988.630786 ALGERIA | 1995 | 464171330.977491 ALGERIA | 1994 | 457103784.883549 ALGERIA | 1993 | 459804465.99042 ALGERIA | 1992 | 459637829.512455 ARGENTINA | 1998 | 274393685.496463 ARGENTINA | 1997 | 464209543.647847 ARGENTINA | 1996 | 466722854.221179 ARGENTINA | 1995 | 463603577.333673 ARGENTINA | 1994 | 476842757.667933 ARGENTINA | 1993 | 468972099.6687 ARGENTINA | 1992 | 467958790.757734 BRAZIL | 1998 | 271931834.172111 BRAZIL | 1997 | 466237140.858833 BRAZIL | 1996 | 464588254.503441 BRAZIL | 1995 | 461610894.919527 BRAZIL | 1994 | 461720842.716153 BRAZIL | 1993 | 466851922.138605 BRAZIL | 1992 | 470644301.14534 CANADA | 1998 | 274891259.915841 CANADA | 1997 | 473316950.329487 CANADA | 1996 | 477619158.187291 CANADA | 1995 | 472847352.649276 CANADA | 1994 | 479120552.979258 CANADA | 1993 | 476505445.920887 CANADA | 1992 | 472972976.65705 CHINA | 1998 | 275427133.692088 CHINA | 1997 | 467160077.862629 CHINA | 1996 | 463973129.705971 CHINA | 1995 | 464681229.939684 CHINA | 1994 | 471017611.699883 CHINA | 1993 | 467822306.356998 CHINA | 1992 | 467035232.751615 EGYPT | 1998 | 266583131.905899 EGYPT | 1997 | 456865355.789732 EGYPT | 1996 | 456704824.526458 EGYPT | 1995 | 451665343.197155 EGYPT | 1994 | 448861764.365114 EGYPT | 1993 | 457284732.502419 EGYPT | 1992 | 460148441.880317 ETHIOPIA | 1998 | 269273138.386206 ETHIOPIA | 1997 | 451555965.319622 ETHIOPIA | 1996 | 455432674.44113 ETHIOPIA | 1995 | 455556675.448952 ETHIOPIA | 1994 | 455558159.586307 ETHIOPIA | 1993 | 456778061.250127 ETHIOPIA | 1992 | 455051563.436975 FRANCE | 1998 | 266588445.217069 FRANCE | 1997 | 460663533.078021 FRANCE | 1996 | 463736528.103556 FRANCE | 1995 | 467677325.3534 FRANCE | 1994 | 464344661.385788 FRANCE | 1993 | 464503766.191087 FRANCE | 1992 | 467299827.124998 GERMANY | 1998 | 283159047.775781 GERMANY | 1997 | 471209287.192532 GERMANY | 1996 | 473802283.336101 GERMANY | 1995 | 480646365.582275 GERMANY | 1994 | 469937202.103911 GERMANY | 1993 | 484760855.974972 GERMANY | 1992 | 476330001.345792 INDIA | 1998 | 273096795.563929 INDIA | 1997 | 472065843.242219 INDIA | 1996 | 479396250.251548 INDIA | 1995 | 461017931.013897 INDIA | 1994 | 467826369.50678 INDIA | 1993 | 467743584.847166 INDIA | 1992 | 465918609.406143 INDONESIA | 1998 | 269954650.8842 INDONESIA | 1997 | 460561757.998586 INDONESIA | 1996 | 467877071.246847 INDONESIA | 1995 | 449149534.643594 INDONESIA | 1994 | 461109307.485799 INDONESIA | 1993 | 463195509.830111 INDONESIA | 1992 | 463737990.774733 IRAN | 1998 | 275330010.149806 IRAN | 1997 | 463366739.815562 IRAN | 1996 | 468467016.112991 IRAN | 1995 | 467928385.606873 IRAN | 1994 | 462509970.849832 IRAN | 1993 | 469530866.028074 IRAN | 1992 | 474121430.891205 IRAQ | 1998 | 279330882.302772 IRAQ | 1997 | 476996923.308497 IRAQ | 1996 | 480781858.823 IRAQ | 1995 | 475590500.840712 IRAQ | 1994 | 481087893.15012 IRAQ | 1993 | 470490893.360664 IRAQ | 1992 | 485129541.535151 JAPAN | 1998 | 268280382.920457 JAPAN | 1997 | 460518074.31885 JAPAN | 1996 | 462299795.341091 JAPAN | 1995 | 457830639.552587 JAPAN | 1994 | 458789685.819041 JAPAN | 1993 | 459450018.055523 JAPAN | 1992 | 461340717.726698 JORDAN | 1998 | 268835696.332358 JORDAN | 1997 | 456380283.373874 JORDAN | 1996 | 451709315.472735 JORDAN | 1995 | 459235907.669249 JORDAN | 1994 | 455574534.669725 JORDAN | 1993 | 455007278.439066 JORDAN | 1992 | 459124603.381044 KENYA | 1998 | 268742318.332943 KENYA | 1997 | 466826663.892407 KENYA | 1996 | 465941331.13357 KENYA | 1995 | 467365926.164729 KENYA | 1994 | 471019297.376098 KENYA | 1993 | 470289590.103187 KENYA | 1992 | 471962213.825444 MOROCCO | 1998 | 273016362.52999 MOROCCO | 1997 | 464450862.945033 MOROCCO | 1996 | 468334305.651047 MOROCCO | 1995 | 463035619.795584 MOROCCO | 1994 | 468182443.080354 MOROCCO | 1993 | 468663222.262576 MOROCCO | 1992 | 465381663.945777 MOZAMBIQUE | 1998 | 272161769.387615 MOZAMBIQUE | 1997 | 462019410.099994 MOZAMBIQUE | 1996 | 462082355.117066 MOZAMBIQUE | 1995 | 466239630.398041 MOZAMBIQUE | 1994 | 454747412.503903 MOZAMBIQUE | 1993 | 460811723.443172 MOZAMBIQUE | 1992 | 461183713.402401 PERU | 1998 | 267866231.894605 PERU | 1997 | 456283472.516331 PERU | 1996 | 457903422.822355 PERU | 1995 | 470259635.888717 PERU | 1994 | 455622669.502669 PERU | 1993 | 455686645.568564 PERU | 1992 | 462045700.801379 ROMANIA | 1998 | 273164089.555156 ROMANIA | 1997 | 477778505.517503 ROMANIA | 1996 | 476491703.645769 ROMANIA | 1995 | 474286150.128904 ROMANIA | 1994 | 472994552.633047 ROMANIA | 1993 | 478842791.46676 ROMANIA | 1992 | 470984167.799041 RUSSIA | 1998 | 279833000.344892 RUSSIA | 1997 | 471615274.773367 RUSSIA | 1996 | 480641196.144156 RUSSIA | 1995 | 483796797.559804 RUSSIA | 1994 | 479575696.501482 RUSSIA | 1993 | 470546948.929101 RUSSIA | 1992 | 476414190.21187 SAUDI ARABIA | 1998 | 276790569.760355 SAUDI ARABIA | 1997 | 464408057.056417 SAUDI ARABIA | 1996 | 472597512.634541 SAUDI ARABIA | 1995 | 470153158.56266 SAUDI ARABIA | 1994 | 471644813.820854 SAUDI ARABIA | 1993 | 471230266.569796 SAUDI ARABIA | 1992 | 473912426.903013 UNITED KINGDOM | 1998 | 268346625.96011 UNITED KINGDOM | 1997 | 457615723.987545 UNITED KINGDOM | 1996 | 456695541.566489 UNITED KINGDOM | 1995 | 457391255.946027 UNITED KINGDOM | 1994 | 452246667.837578 UNITED KINGDOM | 1993 | 452249403.812535 UNITED KINGDOM | 1992 | 454050039.712942 UNITED STATES | 1998 | 282616911.142001 UNITED STATES | 1997 | 471651855.889106 UNITED STATES | 1996 | 478123341.388869 UNITED STATES | 1995 | 471089692.498962 UNITED STATES | 1994 | 476723700.030356 UNITED STATES | 1993 | 480010738.641634 UNITED STATES | 1992 | 476142593.292711 VIETNAM | 1998 | 271303417.2073 VIETNAM | 1997 | 467438382.793432 VIETNAM | 1996 | 467861486.314832 VIETNAM | 1995 | 465129525.204641 VIETNAM | 1994 | 464996650.324228 VIETNAM | 1993 | 462517519.766029 VIETNAM | 1992 | 455958120.563089 (175 rows) COMMIT; COMMIT