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 '%cream%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264510671.026575 ALGERIA | 1997 | 452588175.255436 ALGERIA | 1996 | 455036485.741957 ALGERIA | 1995 | 455261441.661002 ALGERIA | 1994 | 454875694.30591 ALGERIA | 1993 | 456280929.48592 ALGERIA | 1992 | 456844896.981124 ARGENTINA | 1998 | 268188992.943808 ARGENTINA | 1997 | 468869391.674442 ARGENTINA | 1996 | 468193132.359353 ARGENTINA | 1995 | 467875748.543855 ARGENTINA | 1994 | 463079899.04556 ARGENTINA | 1993 | 464658794.036609 ARGENTINA | 1992 | 466270983.995687 BRAZIL | 1998 | 269005630.33713 BRAZIL | 1997 | 475808252.678617 BRAZIL | 1996 | 470157839.953023 BRAZIL | 1995 | 467463112.374512 BRAZIL | 1994 | 470368369.635893 BRAZIL | 1993 | 471848203.305493 BRAZIL | 1992 | 470103929.223085 CANADA | 1998 | 279610293.283436 CANADA | 1997 | 477793640.883377 CANADA | 1996 | 488712245.844458 CANADA | 1995 | 482632922.114576 CANADA | 1994 | 484542995.717064 CANADA | 1993 | 472051635.816605 CANADA | 1992 | 484520599.308114 CHINA | 1998 | 274785825.296828 CHINA | 1997 | 464598264.63438 CHINA | 1996 | 466666200.096905 CHINA | 1995 | 463457706.073978 CHINA | 1994 | 467046771.852369 CHINA | 1993 | 468732273.940462 CHINA | 1992 | 461669379.505424 EGYPT | 1998 | 270164442.465401 EGYPT | 1997 | 460882598.919942 EGYPT | 1996 | 455902291.400942 EGYPT | 1995 | 467807179.897213 EGYPT | 1994 | 459243234.8082 EGYPT | 1993 | 459663788.74515 EGYPT | 1992 | 456768378.201956 ETHIOPIA | 1998 | 274093997.686796 ETHIOPIA | 1997 | 449067720.107602 ETHIOPIA | 1996 | 456353572.943645 ETHIOPIA | 1995 | 468197606.171891 ETHIOPIA | 1994 | 455340615.798815 ETHIOPIA | 1993 | 460619065.526895 ETHIOPIA | 1992 | 468026632.401469 FRANCE | 1998 | 271621474.897921 FRANCE | 1997 | 468588293.223139 FRANCE | 1996 | 473909554.822626 FRANCE | 1995 | 461317463.975728 FRANCE | 1994 | 468905923.988609 FRANCE | 1993 | 465566642.297556 FRANCE | 1992 | 464226539.439813 GERMANY | 1998 | 274993427.266486 GERMANY | 1997 | 466050300.944434 GERMANY | 1996 | 472936036.730716 GERMANY | 1995 | 471399541.236666 GERMANY | 1994 | 470658186.451667 GERMANY | 1993 | 467382858.429683 GERMANY | 1992 | 464892582.241212 INDIA | 1998 | 273002730.418438 INDIA | 1997 | 477975809.384308 INDIA | 1996 | 474579213.143989 INDIA | 1995 | 472546798.760879 INDIA | 1994 | 473369867.277918 INDIA | 1993 | 471643160.704043 INDIA | 1992 | 475631192.123923 INDONESIA | 1998 | 273363976.829348 INDONESIA | 1997 | 462900324.385144 INDONESIA | 1996 | 456892034.800649 INDONESIA | 1995 | 454140822.777953 INDONESIA | 1994 | 453151817.045165 INDONESIA | 1993 | 459111962.151193 INDONESIA | 1992 | 456433373.616287 IRAN | 1998 | 267436909.970118 IRAN | 1997 | 468955935.546416 IRAN | 1996 | 463222131.957292 IRAN | 1995 | 464925138.848576 IRAN | 1994 | 461993825.43371 IRAN | 1993 | 473614719.192552 IRAN | 1992 | 466689618.568994 IRAQ | 1998 | 277462305.038174 IRAQ | 1997 | 469156380.435638 IRAQ | 1996 | 482217850.704869 IRAQ | 1995 | 474960319.884227 IRAQ | 1994 | 478737958.315759 IRAQ | 1993 | 469717045.860701 IRAQ | 1992 | 476228551.816076 JAPAN | 1998 | 268861761.712421 JAPAN | 1997 | 459023002.182615 JAPAN | 1996 | 459568165.277638 JAPAN | 1995 | 456542621.953233 JAPAN | 1994 | 459935140.552733 JAPAN | 1993 | 448208806.6816 JAPAN | 1992 | 449910443.957673 JORDAN | 1998 | 270482198.232331 JORDAN | 1997 | 458377999.675323 JORDAN | 1996 | 458814805.099655 JORDAN | 1995 | 456601153.186032 JORDAN | 1994 | 459421414.312363 JORDAN | 1993 | 465199057.876466 JORDAN | 1992 | 461445358.308561 KENYA | 1998 | 270435207.982151 KENYA | 1997 | 480817879.726432 KENYA | 1996 | 479553906.378345 KENYA | 1995 | 473609636.548804 KENYA | 1994 | 477848552.743906 KENYA | 1993 | 472064685.732685 KENYA | 1992 | 475687795.579877 MOROCCO | 1998 | 277336852.881245 MOROCCO | 1997 | 461584359.533577 MOROCCO | 1996 | 471651396.293053 MOROCCO | 1995 | 467763152.434676 MOROCCO | 1994 | 470942716.056624 MOROCCO | 1993 | 472830537.704084 MOROCCO | 1992 | 471718636.020785 MOZAMBIQUE | 1998 | 271243594.50289 MOZAMBIQUE | 1997 | 462779008.371947 MOZAMBIQUE | 1996 | 462977852.106871 MOZAMBIQUE | 1995 | 455144108.830949 MOZAMBIQUE | 1994 | 457234213.143931 MOZAMBIQUE | 1993 | 457983943.126544 MOZAMBIQUE | 1992 | 454456931.522823 PERU | 1998 | 271985830.868371 PERU | 1997 | 467609032.203626 PERU | 1996 | 470711910.450482 PERU | 1995 | 462745613.245291 PERU | 1994 | 459989897.625048 PERU | 1993 | 463742677.411641 PERU | 1992 | 467092727.047143 ROMANIA | 1998 | 283935621.128768 ROMANIA | 1997 | 471628732.79837 ROMANIA | 1996 | 474983252.574283 ROMANIA | 1995 | 462221052.03355 ROMANIA | 1994 | 469084605.480435 ROMANIA | 1993 | 470729432.124833 ROMANIA | 1992 | 466786471.144199 RUSSIA | 1998 | 277028144.659396 RUSSIA | 1997 | 472877567.839419 RUSSIA | 1996 | 469224126.374779 RUSSIA | 1995 | 470028624.101021 RUSSIA | 1994 | 464446999.06603 RUSSIA | 1993 | 471062751.571118 RUSSIA | 1992 | 470179227.336711 SAUDI ARABIA | 1998 | 264316384.992724 SAUDI ARABIA | 1997 | 460315019.54145 SAUDI ARABIA | 1996 | 453618737.040587 SAUDI ARABIA | 1995 | 459772527.706485 SAUDI ARABIA | 1994 | 458303522.916572 SAUDI ARABIA | 1993 | 461461411.752954 SAUDI ARABIA | 1992 | 463440750.692751 UNITED KINGDOM | 1998 | 274499339.566543 UNITED KINGDOM | 1997 | 467913344.517928 UNITED KINGDOM | 1996 | 469701450.479237 UNITED KINGDOM | 1995 | 466885608.846128 UNITED KINGDOM | 1994 | 471230419.650096 UNITED KINGDOM | 1993 | 470531480.745141 UNITED KINGDOM | 1992 | 462020714.331533 UNITED STATES | 1998 | 275913511.643925 UNITED STATES | 1997 | 474904126.673693 UNITED STATES | 1996 | 474872062.106181 UNITED STATES | 1995 | 475101765.023913 UNITED STATES | 1994 | 481239934.883519 UNITED STATES | 1993 | 475294491.628583 UNITED STATES | 1992 | 474161403.869798 VIETNAM | 1998 | 273151456.553314 VIETNAM | 1997 | 465062129.895047 VIETNAM | 1996 | 470859102.304593 VIETNAM | 1995 | 458383123.276494 VIETNAM | 1994 | 460144893.912034 VIETNAM | 1993 | 471063025.869742 VIETNAM | 1992 | 461964667.774119 (175 rows) COMMIT; COMMIT