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 '%frosted%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267616178.458502 ALGERIA | 1997 | 453693200.774457 ALGERIA | 1996 | 448048596.090775 ALGERIA | 1995 | 450347167.354052 ALGERIA | 1994 | 453639340.785671 ALGERIA | 1993 | 446127355.128553 ALGERIA | 1992 | 449340445.061215 ARGENTINA | 1998 | 279911674.346919 ARGENTINA | 1997 | 470049587.486928 ARGENTINA | 1996 | 472658213.191314 ARGENTINA | 1995 | 471805368.806364 ARGENTINA | 1994 | 472491249.133398 ARGENTINA | 1993 | 465868048.874018 ARGENTINA | 1992 | 463882041.761142 BRAZIL | 1998 | 271181703.325228 BRAZIL | 1997 | 461424401.98521 BRAZIL | 1996 | 464472551.654758 BRAZIL | 1995 | 458464044.341561 BRAZIL | 1994 | 459865063.027952 BRAZIL | 1993 | 461134575.469588 BRAZIL | 1992 | 466565463.67363 CANADA | 1998 | 285367733.774095 CANADA | 1997 | 484710680.022992 CANADA | 1996 | 485790261.635641 CANADA | 1995 | 489690501.404838 CANADA | 1994 | 492723395.472581 CANADA | 1993 | 484974358.865018 CANADA | 1992 | 488826402.307965 CHINA | 1998 | 266303697.736947 CHINA | 1997 | 465136717.640823 CHINA | 1996 | 472635731.61659 CHINA | 1995 | 464547031.921048 CHINA | 1994 | 464411514.344998 CHINA | 1993 | 465622813.842329 CHINA | 1992 | 466041709.591775 EGYPT | 1998 | 278040237.462916 EGYPT | 1997 | 472524399.118313 EGYPT | 1996 | 488589132.245541 EGYPT | 1995 | 483387434.990563 EGYPT | 1994 | 481256485.052536 EGYPT | 1993 | 480705220.623294 EGYPT | 1992 | 474059518.209408 ETHIOPIA | 1998 | 266084202.194683 ETHIOPIA | 1997 | 456000378.44123 ETHIOPIA | 1996 | 461434999.293322 ETHIOPIA | 1995 | 467247126.203214 ETHIOPIA | 1994 | 457280996.479503 ETHIOPIA | 1993 | 466845926.877867 ETHIOPIA | 1992 | 467614814.599991 FRANCE | 1998 | 268896630.644354 FRANCE | 1997 | 447485694.789849 FRANCE | 1996 | 458384669.810532 FRANCE | 1995 | 456485707.376699 FRANCE | 1994 | 455149949.456088 FRANCE | 1993 | 460275903.733532 FRANCE | 1992 | 457908436.375683 GERMANY | 1998 | 278276235.489043 GERMANY | 1997 | 477622134.167699 GERMANY | 1996 | 473404393.410689 GERMANY | 1995 | 476068165.393264 GERMANY | 1994 | 471403344.736827 GERMANY | 1993 | 468784249.606864 GERMANY | 1992 | 471313981.722011 INDIA | 1998 | 279950096.247474 INDIA | 1997 | 475651896.922495 INDIA | 1996 | 484042819.496385 INDIA | 1995 | 470708450.317288 INDIA | 1994 | 472359023.701765 INDIA | 1993 | 475987011.192465 INDIA | 1992 | 477904190.783873 INDONESIA | 1998 | 267983602.508589 INDONESIA | 1997 | 461009912.99769 INDONESIA | 1996 | 469253724.365556 INDONESIA | 1995 | 460326427.288542 INDONESIA | 1994 | 464837563.481138 INDONESIA | 1993 | 466768116.857146 INDONESIA | 1992 | 458355780.906997 IRAN | 1998 | 271147404.935926 IRAN | 1997 | 461242726.604492 IRAN | 1996 | 469343443.724652 IRAN | 1995 | 471144109.725045 IRAN | 1994 | 461771654.761016 IRAN | 1993 | 455551994.898896 IRAN | 1992 | 464278642.39156 IRAQ | 1998 | 280373083.410521 IRAQ | 1997 | 474659882.333046 IRAQ | 1996 | 476969087.092811 IRAQ | 1995 | 483365022.952122 IRAQ | 1994 | 480267297.539652 IRAQ | 1993 | 478021502.786796 IRAQ | 1992 | 481946291.039772 JAPAN | 1998 | 277365492.524758 JAPAN | 1997 | 467891110.182597 JAPAN | 1996 | 465178687.416677 JAPAN | 1995 | 467120255.198208 JAPAN | 1994 | 464865690.621961 JAPAN | 1993 | 461792153.906216 JAPAN | 1992 | 468223236.488255 JORDAN | 1998 | 265327539.824778 JORDAN | 1997 | 459270822.178487 JORDAN | 1996 | 456993299.290695 JORDAN | 1995 | 457960607.455179 JORDAN | 1994 | 458023560.007558 JORDAN | 1993 | 456099833.968815 JORDAN | 1992 | 454150339.023616 KENYA | 1998 | 276575102.578608 KENYA | 1997 | 464805077.920655 KENYA | 1996 | 467031602.092953 KENYA | 1995 | 463574747.165383 KENYA | 1994 | 462793592.509311 KENYA | 1993 | 463992625.545632 KENYA | 1992 | 465111124.018032 MOROCCO | 1998 | 274560836.435071 MOROCCO | 1997 | 463985925.674435 MOROCCO | 1996 | 462400504.245702 MOROCCO | 1995 | 463465948.381486 MOROCCO | 1994 | 462815213.931654 MOROCCO | 1993 | 468057664.69245 MOROCCO | 1992 | 467498837.480956 MOZAMBIQUE | 1998 | 274332428.893674 MOZAMBIQUE | 1997 | 454171461.247772 MOZAMBIQUE | 1996 | 460079998.096621 MOZAMBIQUE | 1995 | 459207605.962904 MOZAMBIQUE | 1994 | 453480349.066804 MOZAMBIQUE | 1993 | 450511733.9735 MOZAMBIQUE | 1992 | 458059278.378429 PERU | 1998 | 273628756.963793 PERU | 1997 | 456580834.325916 PERU | 1996 | 462999761.372459 PERU | 1995 | 461647857.991463 PERU | 1994 | 456803517.749818 PERU | 1993 | 459235300.239896 PERU | 1992 | 462232529.11423 ROMANIA | 1998 | 280303699.857395 ROMANIA | 1997 | 469057062.16015 ROMANIA | 1996 | 475583273.940037 ROMANIA | 1995 | 472075441.651451 ROMANIA | 1994 | 473836574.284869 ROMANIA | 1993 | 476454699.977212 ROMANIA | 1992 | 472463619.990684 RUSSIA | 1998 | 276194803.690316 RUSSIA | 1997 | 474543398.02163 RUSSIA | 1996 | 475580124.581141 RUSSIA | 1995 | 473320793.955532 RUSSIA | 1994 | 468914268.914044 RUSSIA | 1993 | 469951521.77391 RUSSIA | 1992 | 477991498.959549 SAUDI ARABIA | 1998 | 279405807.332523 SAUDI ARABIA | 1997 | 469076692.166021 SAUDI ARABIA | 1996 | 471409729.243964 SAUDI ARABIA | 1995 | 475477369.979125 SAUDI ARABIA | 1994 | 473719599.475322 SAUDI ARABIA | 1993 | 475071238.202018 SAUDI ARABIA | 1992 | 472463016.265251 UNITED KINGDOM | 1998 | 271492679.386505 UNITED KINGDOM | 1997 | 456678263.29864 UNITED KINGDOM | 1996 | 463874470.784511 UNITED KINGDOM | 1995 | 460868630.766648 UNITED KINGDOM | 1994 | 464541381.228727 UNITED KINGDOM | 1993 | 458988022.036246 UNITED KINGDOM | 1992 | 463384864.377379 UNITED STATES | 1998 | 274660234.163717 UNITED STATES | 1997 | 468009299.401895 UNITED STATES | 1996 | 472935780.354973 UNITED STATES | 1995 | 471628456.474609 UNITED STATES | 1994 | 473932821.482051 UNITED STATES | 1993 | 466449063.070815 UNITED STATES | 1992 | 476151723.8078 VIETNAM | 1998 | 272438956.176229 VIETNAM | 1997 | 462055140.12348 VIETNAM | 1996 | 468391876.551018 VIETNAM | 1995 | 464061209.213685 VIETNAM | 1994 | 464547783.875404 VIETNAM | 1993 | 472032171.649899 VIETNAM | 1992 | 465525897.02722 (175 rows) COMMIT; COMMIT