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 '%lime%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267981367.059751 ALGERIA | 1997 | 452638873.207919 ALGERIA | 1996 | 450652874.230836 ALGERIA | 1995 | 453716557.243347 ALGERIA | 1994 | 459934364.000425 ALGERIA | 1993 | 450123120.201313 ALGERIA | 1992 | 459296090.681362 ARGENTINA | 1998 | 274751493.059745 ARGENTINA | 1997 | 468261058.802257 ARGENTINA | 1996 | 465987619.668693 ARGENTINA | 1995 | 468109138.691134 ARGENTINA | 1994 | 463668281.475188 ARGENTINA | 1993 | 468083147.432423 ARGENTINA | 1992 | 470520309.299842 BRAZIL | 1998 | 277720749.39215 BRAZIL | 1997 | 473475050.885844 BRAZIL | 1996 | 474045749.084243 BRAZIL | 1995 | 473002633.049237 BRAZIL | 1994 | 472909711.214775 BRAZIL | 1993 | 481283923.557012 BRAZIL | 1992 | 477543622.44603 CANADA | 1998 | 277426703.479901 CANADA | 1997 | 471824397.042116 CANADA | 1996 | 475766089.51115 CANADA | 1995 | 480896930.589707 CANADA | 1994 | 476071668.325365 CANADA | 1993 | 472586703.011997 CANADA | 1992 | 475284074.968457 CHINA | 1998 | 272603462.104905 CHINA | 1997 | 458885112.528522 CHINA | 1996 | 459740122.101938 CHINA | 1995 | 460606498.544285 CHINA | 1994 | 466503453.507262 CHINA | 1993 | 455838083.695848 CHINA | 1992 | 468158861.014777 EGYPT | 1998 | 280884064.330798 EGYPT | 1997 | 477725385.455251 EGYPT | 1996 | 479197304.654262 EGYPT | 1995 | 477157844.311708 EGYPT | 1994 | 475173681.295469 EGYPT | 1993 | 480250934.215942 EGYPT | 1992 | 478327605.783398 ETHIOPIA | 1998 | 263879848.411331 ETHIOPIA | 1997 | 451244544.23335 ETHIOPIA | 1996 | 449889048.737412 ETHIOPIA | 1995 | 452655395.27502 ETHIOPIA | 1994 | 451041826.825208 ETHIOPIA | 1993 | 451925387.258637 ETHIOPIA | 1992 | 452815561.352263 FRANCE | 1998 | 280849975.084917 FRANCE | 1997 | 462394854.620676 FRANCE | 1996 | 472864894.123594 FRANCE | 1995 | 461882889.484978 FRANCE | 1994 | 469902687.58291 FRANCE | 1993 | 467319839.494867 FRANCE | 1992 | 469256269.820899 GERMANY | 1998 | 267750986.315604 GERMANY | 1997 | 467792574.061745 GERMANY | 1996 | 468443418.092567 GERMANY | 1995 | 465024068.070377 GERMANY | 1994 | 461075578.346555 GERMANY | 1993 | 471783699.422466 GERMANY | 1992 | 465606849.259692 INDIA | 1998 | 271426043.298166 INDIA | 1997 | 469225964.924843 INDIA | 1996 | 484985973.341269 INDIA | 1995 | 469721739.413075 INDIA | 1994 | 476261663.712312 INDIA | 1993 | 481594378.846106 INDIA | 1992 | 479672621.595133 INDONESIA | 1998 | 265084307.796281 INDONESIA | 1997 | 458218093.785088 INDONESIA | 1996 | 465965473.758965 INDONESIA | 1995 | 460339495.993876 INDONESIA | 1994 | 458590070.35505 INDONESIA | 1993 | 465229851.07916 INDONESIA | 1992 | 463040891.993904 IRAN | 1998 | 270392641.128066 IRAN | 1997 | 461721413.224045 IRAN | 1996 | 474412564.605035 IRAN | 1995 | 462421469.605628 IRAN | 1994 | 460120218.36503 IRAN | 1993 | 464827193.885158 IRAN | 1992 | 468574810.281749 IRAQ | 1998 | 271964737.928984 IRAQ | 1997 | 468053187.193727 IRAQ | 1996 | 466243238.851992 IRAQ | 1995 | 460402650.134925 IRAQ | 1994 | 470057318.924802 IRAQ | 1993 | 465407852.706084 IRAQ | 1992 | 467313389.716343 JAPAN | 1998 | 272242587.57859 JAPAN | 1997 | 457942073.896273 JAPAN | 1996 | 468827782.68177 JAPAN | 1995 | 461840688.046924 JAPAN | 1994 | 463854219.723978 JAPAN | 1993 | 468814367.305596 JAPAN | 1992 | 463310346.02871 JORDAN | 1998 | 264966156.11374 JORDAN | 1997 | 462723182.601607 JORDAN | 1996 | 455435541.050511 JORDAN | 1995 | 454313399.08444 JORDAN | 1994 | 454839264.779781 JORDAN | 1993 | 459381153.427199 JORDAN | 1992 | 459050252.209278 KENYA | 1998 | 278090991.668325 KENYA | 1997 | 481684156.12767 KENYA | 1996 | 472905961.143323 KENYA | 1995 | 474602155.931273 KENYA | 1994 | 467735084.165976 KENYA | 1993 | 467245317.57759 KENYA | 1992 | 472921615.816381 MOROCCO | 1998 | 272050488.707312 MOROCCO | 1997 | 463966875.957077 MOROCCO | 1996 | 464790983.572841 MOROCCO | 1995 | 463521068.989832 MOROCCO | 1994 | 463419458.30971 MOROCCO | 1993 | 471636761.73728 MOROCCO | 1992 | 460099210.727986 MOZAMBIQUE | 1998 | 272017096.318252 MOZAMBIQUE | 1997 | 453415862.05631 MOZAMBIQUE | 1996 | 456716128.304457 MOZAMBIQUE | 1995 | 462585671.464512 MOZAMBIQUE | 1994 | 462446466.562947 MOZAMBIQUE | 1993 | 460462014.759722 MOZAMBIQUE | 1992 | 462173688.195574 PERU | 1998 | 271258784.221964 PERU | 1997 | 467526204.152349 PERU | 1996 | 467784526.625585 PERU | 1995 | 463108620.934104 PERU | 1994 | 460647869.757245 PERU | 1993 | 463946936.105901 PERU | 1992 | 468259062.522447 ROMANIA | 1998 | 270138555.589532 ROMANIA | 1997 | 461594253.889399 ROMANIA | 1996 | 471609243.775643 ROMANIA | 1995 | 467614452.001381 ROMANIA | 1994 | 456122409.970637 ROMANIA | 1993 | 471102730.668465 ROMANIA | 1992 | 466139275.905615 RUSSIA | 1998 | 279366875.280421 RUSSIA | 1997 | 480904327.772965 RUSSIA | 1996 | 477873818.423603 RUSSIA | 1995 | 468418156.563102 RUSSIA | 1994 | 474618949.411773 RUSSIA | 1993 | 475823815.218796 RUSSIA | 1992 | 469440040.955752 SAUDI ARABIA | 1998 | 278212688.100658 SAUDI ARABIA | 1997 | 476688350.477033 SAUDI ARABIA | 1996 | 469976591.43132 SAUDI ARABIA | 1995 | 470318765.862152 SAUDI ARABIA | 1994 | 466179695.775712 SAUDI ARABIA | 1993 | 476791938.658311 SAUDI ARABIA | 1992 | 476335212.029811 UNITED KINGDOM | 1998 | 278168186.024409 UNITED KINGDOM | 1997 | 460942931.803787 UNITED KINGDOM | 1996 | 468309674.966313 UNITED KINGDOM | 1995 | 468783350.909011 UNITED KINGDOM | 1994 | 466161772.497338 UNITED KINGDOM | 1993 | 461881709.40001 UNITED KINGDOM | 1992 | 457366594.014688 UNITED STATES | 1998 | 274573379.29808 UNITED STATES | 1997 | 479472634.420155 UNITED STATES | 1996 | 467188006.955135 UNITED STATES | 1995 | 466376923.75303 UNITED STATES | 1994 | 469479010.485128 UNITED STATES | 1993 | 474867631.038663 UNITED STATES | 1992 | 463925022.082794 VIETNAM | 1998 | 276003877.925855 VIETNAM | 1997 | 474987124.093115 VIETNAM | 1996 | 482989297.240307 VIETNAM | 1995 | 469810703.480605 VIETNAM | 1994 | 480203226.649102 VIETNAM | 1993 | 473705179.166287 VIETNAM | 1992 | 471928031.652126 (175 rows) COMMIT; COMMIT