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 '%yellow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270308101.105642 ALGERIA | 1997 | 471060017.778771 ALGERIA | 1996 | 464808428.208265 ALGERIA | 1995 | 465233151.668789 ALGERIA | 1994 | 464869791.136204 ALGERIA | 1993 | 464413495.150289 ALGERIA | 1992 | 464477456.928123 ARGENTINA | 1998 | 275566390.68958 ARGENTINA | 1997 | 471463847.581694 ARGENTINA | 1996 | 478014170.991459 ARGENTINA | 1995 | 473221013.021998 ARGENTINA | 1994 | 468319858.263208 ARGENTINA | 1993 | 466918207.720301 ARGENTINA | 1992 | 473758836.846378 BRAZIL | 1998 | 279650243.342955 BRAZIL | 1997 | 456906779.912469 BRAZIL | 1996 | 463929655.795926 BRAZIL | 1995 | 459733119.072152 BRAZIL | 1994 | 467477257.30603 BRAZIL | 1993 | 461368022.112284 BRAZIL | 1992 | 468688169.890045 CANADA | 1998 | 270689661.113493 CANADA | 1997 | 483512826.062588 CANADA | 1996 | 478546617.787426 CANADA | 1995 | 476715268.34125 CANADA | 1994 | 480385124.728576 CANADA | 1993 | 474721905.343336 CANADA | 1992 | 468406836.466874 CHINA | 1998 | 267796124.211101 CHINA | 1997 | 453893714.772233 CHINA | 1996 | 464814458.958742 CHINA | 1995 | 462360753.567327 CHINA | 1994 | 454769783.380589 CHINA | 1993 | 455763954.892441 CHINA | 1992 | 460867930.769525 EGYPT | 1998 | 269640005.284679 EGYPT | 1997 | 467665924.392668 EGYPT | 1996 | 472228612.226796 EGYPT | 1995 | 459603100.90035 EGYPT | 1994 | 463255229.097706 EGYPT | 1993 | 470471340.448875 EGYPT | 1992 | 469589378.277589 ETHIOPIA | 1998 | 274646061.556991 ETHIOPIA | 1997 | 458825309.384599 ETHIOPIA | 1996 | 461504633.156465 ETHIOPIA | 1995 | 460792018.607678 ETHIOPIA | 1994 | 460373614.113207 ETHIOPIA | 1993 | 467565595.636836 ETHIOPIA | 1992 | 462574515.673982 FRANCE | 1998 | 270378720.135785 FRANCE | 1997 | 461779943.580373 FRANCE | 1996 | 461087362.268042 FRANCE | 1995 | 465813617.195053 FRANCE | 1994 | 464977240.985311 FRANCE | 1993 | 458336993.00166 FRANCE | 1992 | 462031322.778574 GERMANY | 1998 | 277692493.7273 GERMANY | 1997 | 476101144.66532 GERMANY | 1996 | 478895571.921388 GERMANY | 1995 | 484761742.339183 GERMANY | 1994 | 480638048.453483 GERMANY | 1993 | 488880685.358648 GERMANY | 1992 | 474329410.537336 INDIA | 1998 | 280002906.539344 INDIA | 1997 | 467309299.019149 INDIA | 1996 | 469380570.445879 INDIA | 1995 | 468983951.743926 INDIA | 1994 | 472807881.62086 INDIA | 1993 | 470093414.747093 INDIA | 1992 | 460521916.498379 INDONESIA | 1998 | 272265976.837126 INDONESIA | 1997 | 460972537.257112 INDONESIA | 1996 | 453833230.256153 INDONESIA | 1995 | 458574440.369379 INDONESIA | 1994 | 468480715.926527 INDONESIA | 1993 | 463861372.86465 INDONESIA | 1992 | 458833506.717013 IRAN | 1998 | 270886805.612102 IRAN | 1997 | 460025066.20835 IRAN | 1996 | 461016037.782113 IRAN | 1995 | 468372977.446744 IRAN | 1994 | 463873974.98246 IRAN | 1993 | 460356859.627596 IRAN | 1992 | 457798014.93995 IRAQ | 1998 | 278611381.875018 IRAQ | 1997 | 476757394.240266 IRAQ | 1996 | 480524051.484896 IRAQ | 1995 | 482607479.097996 IRAQ | 1994 | 484122343.474371 IRAQ | 1993 | 471459292.088569 IRAQ | 1992 | 475582446.248825 JAPAN | 1998 | 276586915.460989 JAPAN | 1997 | 463507600.589664 JAPAN | 1996 | 472608986.265436 JAPAN | 1995 | 467360409.429414 JAPAN | 1994 | 467981866.841585 JAPAN | 1993 | 472631042.129565 JAPAN | 1992 | 473700028.923795 JORDAN | 1998 | 266762368.48743 JORDAN | 1997 | 454138000.489838 JORDAN | 1996 | 451625820.354199 JORDAN | 1995 | 455155076.999045 JORDAN | 1994 | 464023911.766974 JORDAN | 1993 | 457797345.236686 JORDAN | 1992 | 462867511.305729 KENYA | 1998 | 280774248.570988 KENYA | 1997 | 470536809.882897 KENYA | 1996 | 472273894.331694 KENYA | 1995 | 470356064.901985 KENYA | 1994 | 476387760.738268 KENYA | 1993 | 479086960.261937 KENYA | 1992 | 477923338.236179 MOROCCO | 1998 | 270907101.036275 MOROCCO | 1997 | 462571455.047427 MOROCCO | 1996 | 469542650.570879 MOROCCO | 1995 | 465740482.471238 MOROCCO | 1994 | 465709410.557077 MOROCCO | 1993 | 463067315.824052 MOROCCO | 1992 | 462013924.102625 MOZAMBIQUE | 1998 | 271089813.525369 MOZAMBIQUE | 1997 | 458466832.406264 MOZAMBIQUE | 1996 | 462163433.539838 MOZAMBIQUE | 1995 | 463625645.957528 MOZAMBIQUE | 1994 | 462868838.812133 MOZAMBIQUE | 1993 | 455074906.173793 MOZAMBIQUE | 1992 | 456596123.703207 PERU | 1998 | 276265122.018822 PERU | 1997 | 467018614.966339 PERU | 1996 | 459967291.133616 PERU | 1995 | 466964692.279267 PERU | 1994 | 455000364.192278 PERU | 1993 | 457934264.621453 PERU | 1992 | 466589942.145409 ROMANIA | 1998 | 273677998.511525 ROMANIA | 1997 | 471422072.843567 ROMANIA | 1996 | 472552309.895127 ROMANIA | 1995 | 467211561.086481 ROMANIA | 1994 | 462219104.346724 ROMANIA | 1993 | 473613753.884542 ROMANIA | 1992 | 477044537.068596 RUSSIA | 1998 | 276306863.704348 RUSSIA | 1997 | 470304801.483343 RUSSIA | 1996 | 484285561.680391 RUSSIA | 1995 | 476254617.698303 RUSSIA | 1994 | 474505177.48016 RUSSIA | 1993 | 475722150.374116 RUSSIA | 1992 | 480214262.141497 SAUDI ARABIA | 1998 | 269816724.617363 SAUDI ARABIA | 1997 | 460423990.992077 SAUDI ARABIA | 1996 | 465823040.390209 SAUDI ARABIA | 1995 | 460935774.27761 SAUDI ARABIA | 1994 | 459532240.110263 SAUDI ARABIA | 1993 | 462811617.96996 SAUDI ARABIA | 1992 | 468276388.41676 UNITED KINGDOM | 1998 | 270411517.709422 UNITED KINGDOM | 1997 | 463299731.413598 UNITED KINGDOM | 1996 | 456713760.797302 UNITED KINGDOM | 1995 | 461228822.76788 UNITED KINGDOM | 1994 | 458548737.330662 UNITED KINGDOM | 1993 | 461271561.137862 UNITED KINGDOM | 1992 | 464798591.21504 UNITED STATES | 1998 | 277445353.804801 UNITED STATES | 1997 | 476589655.319806 UNITED STATES | 1996 | 474476829.988911 UNITED STATES | 1995 | 476825126.208383 UNITED STATES | 1994 | 476289627.563949 UNITED STATES | 1993 | 472908449.296083 UNITED STATES | 1992 | 475506001.916615 VIETNAM | 1998 | 270805378.854867 VIETNAM | 1997 | 455024288.784776 VIETNAM | 1996 | 468419122.896708 VIETNAM | 1995 | 464858104.901265 VIETNAM | 1994 | 460322822.915598 VIETNAM | 1993 | 463481646.441241 VIETNAM | 1992 | 460868392.27447 (175 rows) COMMIT; COMMIT