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 '%lemon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265562653.029333 ALGERIA | 1997 | 446489073.530528 ALGERIA | 1996 | 455861233.675151 ALGERIA | 1995 | 451726882.131445 ALGERIA | 1994 | 458356048.20005 ALGERIA | 1993 | 453433705.70331 ALGERIA | 1992 | 446284174.207997 ARGENTINA | 1998 | 276917563.294541 ARGENTINA | 1997 | 464162892.089955 ARGENTINA | 1996 | 464116936.312132 ARGENTINA | 1995 | 467355130.054121 ARGENTINA | 1994 | 469874119.317321 ARGENTINA | 1993 | 465966065.264155 ARGENTINA | 1992 | 458848723.538706 BRAZIL | 1998 | 280622580.292472 BRAZIL | 1997 | 479289376.410233 BRAZIL | 1996 | 480467302.994452 BRAZIL | 1995 | 465810079.563538 BRAZIL | 1994 | 465331301.235192 BRAZIL | 1993 | 473559807.879479 BRAZIL | 1992 | 475502828.640029 CANADA | 1998 | 276760886.645946 CANADA | 1997 | 475448895.91333 CANADA | 1996 | 480583945.871425 CANADA | 1995 | 476942887.174749 CANADA | 1994 | 480561412.605411 CANADA | 1993 | 473894556.477186 CANADA | 1992 | 476456051.986425 CHINA | 1998 | 266464656.497043 CHINA | 1997 | 466974893.235191 CHINA | 1996 | 468789201.385942 CHINA | 1995 | 462218967.134859 CHINA | 1994 | 458725142.865189 CHINA | 1993 | 464240462.467278 CHINA | 1992 | 462731144.909912 EGYPT | 1998 | 268193998.545718 EGYPT | 1997 | 458898654.925645 EGYPT | 1996 | 463121966.607906 EGYPT | 1995 | 454595024.977607 EGYPT | 1994 | 467137076.592467 EGYPT | 1993 | 472158110.807209 EGYPT | 1992 | 464828406.690979 ETHIOPIA | 1998 | 275056326.563222 ETHIOPIA | 1997 | 460683876.905054 ETHIOPIA | 1996 | 470115593.906725 ETHIOPIA | 1995 | 468694780.854864 ETHIOPIA | 1994 | 468768707.535948 ETHIOPIA | 1993 | 467038808.089525 ETHIOPIA | 1992 | 463668613.085396 FRANCE | 1998 | 268683940.403979 FRANCE | 1997 | 453313777.247808 FRANCE | 1996 | 466682038.956928 FRANCE | 1995 | 456021793.284313 FRANCE | 1994 | 460418349.454989 FRANCE | 1993 | 458359357.276952 FRANCE | 1992 | 450404139.495705 GERMANY | 1998 | 278446847.263562 GERMANY | 1997 | 479267871.771054 GERMANY | 1996 | 478936143.414886 GERMANY | 1995 | 475235899.099369 GERMANY | 1994 | 472704137.495817 GERMANY | 1993 | 465994555.158072 GERMANY | 1992 | 482490933.49184 INDIA | 1998 | 279487771.969252 INDIA | 1997 | 478032193.136085 INDIA | 1996 | 478159912.57735 INDIA | 1995 | 473183234.080745 INDIA | 1994 | 476573193.846988 INDIA | 1993 | 472665790.031441 INDIA | 1992 | 482556501.544726 INDONESIA | 1998 | 269973953.810949 INDONESIA | 1997 | 453613530.579571 INDONESIA | 1996 | 452435734.913234 INDONESIA | 1995 | 452342187.757228 INDONESIA | 1994 | 453208844.5409 INDONESIA | 1993 | 459855716.064097 INDONESIA | 1992 | 460304460.767081 IRAN | 1998 | 269542439.897654 IRAN | 1997 | 463533583.357333 IRAN | 1996 | 472341162.839701 IRAN | 1995 | 464033241.908955 IRAN | 1994 | 464882394.222311 IRAN | 1993 | 463857294.018157 IRAN | 1992 | 471727649.215094 IRAQ | 1998 | 280136676.876562 IRAQ | 1997 | 476330478.419878 IRAQ | 1996 | 472912572.366762 IRAQ | 1995 | 477054063.057735 IRAQ | 1994 | 476598139.878075 IRAQ | 1993 | 477455225.84922 IRAQ | 1992 | 473410164.728925 JAPAN | 1998 | 266495143.973842 JAPAN | 1997 | 464425609.945825 JAPAN | 1996 | 466369678.609785 JAPAN | 1995 | 466388002.894334 JAPAN | 1994 | 467511447.366593 JAPAN | 1993 | 467447126.865917 JAPAN | 1992 | 468391323.577068 JORDAN | 1998 | 273501664.210219 JORDAN | 1997 | 463761741.934635 JORDAN | 1996 | 469971425.836889 JORDAN | 1995 | 473361028.394526 JORDAN | 1994 | 465631930.618978 JORDAN | 1993 | 462939643.108563 JORDAN | 1992 | 456364986.141859 KENYA | 1998 | 274989077.011677 KENYA | 1997 | 465999157.967102 KENYA | 1996 | 468303222.529326 KENYA | 1995 | 467070004.459419 KENYA | 1994 | 465744448.570444 KENYA | 1993 | 470438466.832518 KENYA | 1992 | 471870765.739422 MOROCCO | 1998 | 270854291.333221 MOROCCO | 1997 | 465228212.01471 MOROCCO | 1996 | 465986308.551744 MOROCCO | 1995 | 465394509.327573 MOROCCO | 1994 | 473194594.252883 MOROCCO | 1993 | 467837339.123458 MOROCCO | 1992 | 467299726.889203 MOZAMBIQUE | 1998 | 268528737.197393 MOZAMBIQUE | 1997 | 464693425.89239 MOZAMBIQUE | 1996 | 466727501.083086 MOZAMBIQUE | 1995 | 464910440.023651 MOZAMBIQUE | 1994 | 456978595.97084 MOZAMBIQUE | 1993 | 455923364.18109 MOZAMBIQUE | 1992 | 459785942.292013 PERU | 1998 | 274224340.862607 PERU | 1997 | 472174915.105258 PERU | 1996 | 471859553.928224 PERU | 1995 | 473234271.311265 PERU | 1994 | 464680330.05245 PERU | 1993 | 471628863.222662 PERU | 1992 | 475831454.909922 ROMANIA | 1998 | 270908751.12429 ROMANIA | 1997 | 472829061.25738 ROMANIA | 1996 | 469485371.283617 ROMANIA | 1995 | 466485387.508724 ROMANIA | 1994 | 462576340.621214 ROMANIA | 1993 | 473312282.260831 ROMANIA | 1992 | 474430529.73302 RUSSIA | 1998 | 279324093.762582 RUSSIA | 1997 | 465925473.825421 RUSSIA | 1996 | 475738818.115259 RUSSIA | 1995 | 469021203.529489 RUSSIA | 1994 | 467107407.061856 RUSSIA | 1993 | 472854586.523817 RUSSIA | 1992 | 465957696.102564 SAUDI ARABIA | 1998 | 274395615.104129 SAUDI ARABIA | 1997 | 468132698.178584 SAUDI ARABIA | 1996 | 468333316.69704 SAUDI ARABIA | 1995 | 473422286.422901 SAUDI ARABIA | 1994 | 462498646.098928 SAUDI ARABIA | 1993 | 470838467.061104 SAUDI ARABIA | 1992 | 475384791.832352 UNITED KINGDOM | 1998 | 270716776.780249 UNITED KINGDOM | 1997 | 459210222.047481 UNITED KINGDOM | 1996 | 462656747.165247 UNITED KINGDOM | 1995 | 463238197.325853 UNITED KINGDOM | 1994 | 458950452.868506 UNITED KINGDOM | 1993 | 462319975.706125 UNITED KINGDOM | 1992 | 460197772.861667 UNITED STATES | 1998 | 273916232.33342 UNITED STATES | 1997 | 472282457.917642 UNITED STATES | 1996 | 481169134.841885 UNITED STATES | 1995 | 472190069.246789 UNITED STATES | 1994 | 474283661.485783 UNITED STATES | 1993 | 487454556.842807 UNITED STATES | 1992 | 478296604.761414 VIETNAM | 1998 | 270018498.176721 VIETNAM | 1997 | 462755034.18387 VIETNAM | 1996 | 472422638.322884 VIETNAM | 1995 | 467725511.386704 VIETNAM | 1994 | 471597407.679966 VIETNAM | 1993 | 470947069.835518 VIETNAM | 1992 | 466595372.659788 (175 rows) COMMIT; COMMIT