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 | 266416226.721397 ALGERIA | 1997 | 447823351.025088 ALGERIA | 1996 | 453093838.23802 ALGERIA | 1995 | 456857770.863371 ALGERIA | 1994 | 444884869.445617 ALGERIA | 1993 | 452229475.778647 ALGERIA | 1992 | 451397353.373251 ARGENTINA | 1998 | 279356078.632894 ARGENTINA | 1997 | 473446980.728045 ARGENTINA | 1996 | 469349339.017021 ARGENTINA | 1995 | 474151105.675297 ARGENTINA | 1994 | 471727037.873006 ARGENTINA | 1993 | 468229956.523884 ARGENTINA | 1992 | 472945051.872153 BRAZIL | 1998 | 270326657.083268 BRAZIL | 1997 | 464354155.208538 BRAZIL | 1996 | 458872416.179327 BRAZIL | 1995 | 464728563.320807 BRAZIL | 1994 | 461046640.062362 BRAZIL | 1993 | 460060619.061495 BRAZIL | 1992 | 459307196.159068 CANADA | 1998 | 277396435.479084 CANADA | 1997 | 474478456.083391 CANADA | 1996 | 469483020.83181 CANADA | 1995 | 468449442.399265 CANADA | 1994 | 480872085.742379 CANADA | 1993 | 479443545.752583 CANADA | 1992 | 465166596.100889 CHINA | 1998 | 269285462.747817 CHINA | 1997 | 456983099.320014 CHINA | 1996 | 461422764.123431 CHINA | 1995 | 458934471.530001 CHINA | 1994 | 456386328.887119 CHINA | 1993 | 469875413.705289 CHINA | 1992 | 459277277.490274 EGYPT | 1998 | 275107344.654663 EGYPT | 1997 | 469614342.474446 EGYPT | 1996 | 472284932.278538 EGYPT | 1995 | 470895189.65226 EGYPT | 1994 | 468885938.131442 EGYPT | 1993 | 468960734.518822 EGYPT | 1992 | 460155514.236005 ETHIOPIA | 1998 | 265107349.91739 ETHIOPIA | 1997 | 455957353.035563 ETHIOPIA | 1996 | 460459974.611426 ETHIOPIA | 1995 | 457223888.717463 ETHIOPIA | 1994 | 464813496.531089 ETHIOPIA | 1993 | 461793366.62879 ETHIOPIA | 1992 | 456477731.80062 FRANCE | 1998 | 267201065.905191 FRANCE | 1997 | 457101870.75971 FRANCE | 1996 | 455914413.740088 FRANCE | 1995 | 454125786.740605 FRANCE | 1994 | 453983648.650768 FRANCE | 1993 | 453776931.706972 FRANCE | 1992 | 456362248.411811 GERMANY | 1998 | 285158568.507992 GERMANY | 1997 | 473480352.037219 GERMANY | 1996 | 474397993.402471 GERMANY | 1995 | 482152378.996223 GERMANY | 1994 | 471066648.190946 GERMANY | 1993 | 470090485.960293 GERMANY | 1992 | 469586614.756569 INDIA | 1998 | 271921424.536105 INDIA | 1997 | 466589432.328454 INDIA | 1996 | 481287708.852217 INDIA | 1995 | 472269421.447473 INDIA | 1994 | 472404500.398947 INDIA | 1993 | 470399054.723683 INDIA | 1992 | 472022008.586084 INDONESIA | 1998 | 272912112.896899 INDONESIA | 1997 | 463965070.498682 INDONESIA | 1996 | 463478829.519603 INDONESIA | 1995 | 462900790.177354 INDONESIA | 1994 | 469613412.310336 INDONESIA | 1993 | 464305434.838216 INDONESIA | 1992 | 469340607.97753 IRAN | 1998 | 275858619.124599 IRAN | 1997 | 464658316.895635 IRAN | 1996 | 476929038.146609 IRAN | 1995 | 457047899.686648 IRAN | 1994 | 463809526.544131 IRAN | 1993 | 469789335.43983 IRAN | 1992 | 471352185.318667 IRAQ | 1998 | 267383237.945262 IRAQ | 1997 | 456415859.902889 IRAQ | 1996 | 464851856.1919 IRAQ | 1995 | 452071930.352536 IRAQ | 1994 | 454908676.663369 IRAQ | 1993 | 457528445.1229 IRAQ | 1992 | 455432488.892333 JAPAN | 1998 | 271991429.775165 JAPAN | 1997 | 465797314.347691 JAPAN | 1996 | 462056676.479274 JAPAN | 1995 | 468549046.715512 JAPAN | 1994 | 464876163.268821 JAPAN | 1993 | 464930734.84266 JAPAN | 1992 | 459923933.742205 JORDAN | 1998 | 262963586.031102 JORDAN | 1997 | 451236411.765935 JORDAN | 1996 | 448948133.653179 JORDAN | 1995 | 454096310.316359 JORDAN | 1994 | 448990638.874943 JORDAN | 1993 | 454556036.134165 JORDAN | 1992 | 447820465.610495 KENYA | 1998 | 275664576.404377 KENYA | 1997 | 474629780.594834 KENYA | 1996 | 479555637.869731 KENYA | 1995 | 466091940.091633 KENYA | 1994 | 478476922.816021 KENYA | 1993 | 470381888.216846 KENYA | 1992 | 475553134.73546 MOROCCO | 1998 | 270070626.393755 MOROCCO | 1997 | 472391011.780322 MOROCCO | 1996 | 469103393.522661 MOROCCO | 1995 | 463908688.546326 MOROCCO | 1994 | 464914354.529441 MOROCCO | 1993 | 464226493.907216 MOROCCO | 1992 | 473791833.001998 MOZAMBIQUE | 1998 | 268240649.911403 MOZAMBIQUE | 1997 | 459109632.360407 MOZAMBIQUE | 1996 | 457895997.242658 MOZAMBIQUE | 1995 | 458569844.126382 MOZAMBIQUE | 1994 | 452472749.721674 MOZAMBIQUE | 1993 | 457473842.276256 MOZAMBIQUE | 1992 | 457083963.022188 PERU | 1998 | 269703075.30288 PERU | 1997 | 451618547.890577 PERU | 1996 | 456243726.857007 PERU | 1995 | 454112115.241174 PERU | 1994 | 451394633.043685 PERU | 1993 | 454656787.421023 PERU | 1992 | 460049732.310955 ROMANIA | 1998 | 271962234.158175 ROMANIA | 1997 | 469953432.534235 ROMANIA | 1996 | 467074347.1456 ROMANIA | 1995 | 467503750.703945 ROMANIA | 1994 | 463421898.601584 ROMANIA | 1993 | 463709632.789435 ROMANIA | 1992 | 460006572.39287 RUSSIA | 1998 | 272827822.245684 RUSSIA | 1997 | 468405542.592237 RUSSIA | 1996 | 474586410.409877 RUSSIA | 1995 | 465656319.877883 RUSSIA | 1994 | 462640840.101069 RUSSIA | 1993 | 466756294.546153 RUSSIA | 1992 | 468241400.90766 SAUDI ARABIA | 1998 | 272318100.785915 SAUDI ARABIA | 1997 | 465260766.769902 SAUDI ARABIA | 1996 | 471602654.975161 SAUDI ARABIA | 1995 | 471356023.827624 SAUDI ARABIA | 1994 | 471342195.326578 SAUDI ARABIA | 1993 | 467444048.942702 SAUDI ARABIA | 1992 | 471248884.515121 UNITED KINGDOM | 1998 | 274996298.854316 UNITED KINGDOM | 1997 | 461095029.824099 UNITED KINGDOM | 1996 | 459736440.525983 UNITED KINGDOM | 1995 | 459946381.385671 UNITED KINGDOM | 1994 | 454595062.821886 UNITED KINGDOM | 1993 | 463782784.287182 UNITED KINGDOM | 1992 | 461467430.595691 UNITED STATES | 1998 | 266854399.424141 UNITED STATES | 1997 | 462022235.365874 UNITED STATES | 1996 | 467104525.482772 UNITED STATES | 1995 | 471510058.75799 UNITED STATES | 1994 | 470142800.172369 UNITED STATES | 1993 | 464302736.477756 UNITED STATES | 1992 | 457355261.789122 VIETNAM | 1998 | 271133152.462964 VIETNAM | 1997 | 462881361.173679 VIETNAM | 1996 | 461885763.329276 VIETNAM | 1995 | 461085305.090806 VIETNAM | 1994 | 467929930.1917 VIETNAM | 1993 | 465791238.663733 VIETNAM | 1992 | 464643693.864847 (175 rows) COMMIT; COMMIT