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 '%chiffon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271958029.495737 ALGERIA | 1997 | 453514275.098184 ALGERIA | 1996 | 461536904.478712 ALGERIA | 1995 | 460031596.981898 ALGERIA | 1994 | 452293439.068155 ALGERIA | 1993 | 459346886.276512 ALGERIA | 1992 | 454210068.693443 ARGENTINA | 1998 | 268916117.245527 ARGENTINA | 1997 | 472873749.431088 ARGENTINA | 1996 | 466473182.894496 ARGENTINA | 1995 | 463000042.793055 ARGENTINA | 1994 | 474006177.735367 ARGENTINA | 1993 | 467642954.18372 ARGENTINA | 1992 | 462327269.741807 BRAZIL | 1998 | 268632671.431655 BRAZIL | 1997 | 468670665.974066 BRAZIL | 1996 | 462048913.190185 BRAZIL | 1995 | 465893266.421452 BRAZIL | 1994 | 466780689.099948 BRAZIL | 1993 | 455958475.932543 BRAZIL | 1992 | 472041661.987508 CANADA | 1998 | 275289197.170611 CANADA | 1997 | 460180948.332107 CANADA | 1996 | 473713522.205834 CANADA | 1995 | 464388043.686837 CANADA | 1994 | 476094359.845466 CANADA | 1993 | 468420295.24019 CANADA | 1992 | 475226434.947109 CHINA | 1998 | 277823787.735282 CHINA | 1997 | 476262896.887891 CHINA | 1996 | 477846029.717284 CHINA | 1995 | 471206608.226406 CHINA | 1994 | 470335495.730469 CHINA | 1993 | 476266803.661972 CHINA | 1992 | 471563774.382358 EGYPT | 1998 | 272189700.784587 EGYPT | 1997 | 462824736.135125 EGYPT | 1996 | 472705595.081484 EGYPT | 1995 | 466362651.167102 EGYPT | 1994 | 460576114.63884 EGYPT | 1993 | 465182609.296875 EGYPT | 1992 | 461776228.580673 ETHIOPIA | 1998 | 267982868.274578 ETHIOPIA | 1997 | 458107756.778221 ETHIOPIA | 1996 | 454920160.751017 ETHIOPIA | 1995 | 457970764.952674 ETHIOPIA | 1994 | 454291459.679121 ETHIOPIA | 1993 | 459635124.475671 ETHIOPIA | 1992 | 457393556.461847 FRANCE | 1998 | 271962472.66746 FRANCE | 1997 | 468689442.183532 FRANCE | 1996 | 463491693.560822 FRANCE | 1995 | 470820813.132963 FRANCE | 1994 | 464551244.115965 FRANCE | 1993 | 465675089.587447 FRANCE | 1992 | 458559096.2929 GERMANY | 1998 | 278286736.90628 GERMANY | 1997 | 468688513.905155 GERMANY | 1996 | 472386161.35191 GERMANY | 1995 | 474796309.545157 GERMANY | 1994 | 473550343.367125 GERMANY | 1993 | 469063253.720536 GERMANY | 1992 | 471708759.507205 INDIA | 1998 | 277993685.221555 INDIA | 1997 | 471795413.011457 INDIA | 1996 | 477661065.455926 INDIA | 1995 | 470211385.480767 INDIA | 1994 | 474849119.343744 INDIA | 1993 | 471242126.473468 INDIA | 1992 | 478742810.84018 INDONESIA | 1998 | 277035381.652741 INDONESIA | 1997 | 462726625.868205 INDONESIA | 1996 | 468970259.986684 INDONESIA | 1995 | 455873360.52434 INDONESIA | 1994 | 464749745.183297 INDONESIA | 1993 | 467198000.2792 INDONESIA | 1992 | 463593180.103197 IRAN | 1998 | 272065116.47104 IRAN | 1997 | 462557214.060608 IRAN | 1996 | 458665387.209265 IRAN | 1995 | 463637334.048985 IRAN | 1994 | 460245818.534804 IRAN | 1993 | 460091323.96158 IRAN | 1992 | 467282817.025502 IRAQ | 1998 | 276724188.704825 IRAQ | 1997 | 463823738.548603 IRAQ | 1996 | 471195274.202839 IRAQ | 1995 | 470998495.554147 IRAQ | 1994 | 475726794.030613 IRAQ | 1993 | 476769703.410198 IRAQ | 1992 | 472608005.953387 JAPAN | 1998 | 278212562.984738 JAPAN | 1997 | 474942865.294834 JAPAN | 1996 | 469539713.612741 JAPAN | 1995 | 475808145.515801 JAPAN | 1994 | 473624463.022214 JAPAN | 1993 | 472861493.944822 JAPAN | 1992 | 473914953.611502 JORDAN | 1998 | 277389436.636746 JORDAN | 1997 | 462130180.316579 JORDAN | 1996 | 465549689.117505 JORDAN | 1995 | 464587985.356286 JORDAN | 1994 | 463226201.000021 JORDAN | 1993 | 466978298.019972 JORDAN | 1992 | 463010335.141186 KENYA | 1998 | 273277234.29172 KENYA | 1997 | 466476918.563924 KENYA | 1996 | 476435709.357053 KENYA | 1995 | 471603432.911761 KENYA | 1994 | 473269036.212651 KENYA | 1993 | 474278965.503994 KENYA | 1992 | 476063006.977954 MOROCCO | 1998 | 273907251.715655 MOROCCO | 1997 | 466527589.151072 MOROCCO | 1996 | 478360657.642035 MOROCCO | 1995 | 466581754.502436 MOROCCO | 1994 | 476466577.357909 MOROCCO | 1993 | 465533378.175185 MOROCCO | 1992 | 482360256.280499 MOZAMBIQUE | 1998 | 269351346.970834 MOZAMBIQUE | 1997 | 455676204.644963 MOZAMBIQUE | 1996 | 462261786.375074 MOZAMBIQUE | 1995 | 457756660.268929 MOZAMBIQUE | 1994 | 459867336.585588 MOZAMBIQUE | 1993 | 454457557.29301 MOZAMBIQUE | 1992 | 456340311.779376 PERU | 1998 | 275449597.946368 PERU | 1997 | 461895491.02069 PERU | 1996 | 464560352.903755 PERU | 1995 | 472384775.80062 PERU | 1994 | 461404454.678291 PERU | 1993 | 470463920.564417 PERU | 1992 | 461079591.857414 ROMANIA | 1998 | 278260318.302568 ROMANIA | 1997 | 462664131.844376 ROMANIA | 1996 | 465096911.07354 ROMANIA | 1995 | 463555183.050014 ROMANIA | 1994 | 470140061.658407 ROMANIA | 1993 | 466776637.973688 ROMANIA | 1992 | 469333903.060954 RUSSIA | 1998 | 274513617.602741 RUSSIA | 1997 | 467490260.666969 RUSSIA | 1996 | 467354592.920958 RUSSIA | 1995 | 470263870.489483 RUSSIA | 1994 | 471682660.936607 RUSSIA | 1993 | 470493102.219498 RUSSIA | 1992 | 471073594.211794 SAUDI ARABIA | 1998 | 273167281.126585 SAUDI ARABIA | 1997 | 461489861.734555 SAUDI ARABIA | 1996 | 465577104.560943 SAUDI ARABIA | 1995 | 458957211.492218 SAUDI ARABIA | 1994 | 455507932.049534 SAUDI ARABIA | 1993 | 461178260.827668 SAUDI ARABIA | 1992 | 463028492.498481 UNITED KINGDOM | 1998 | 271175811.162876 UNITED KINGDOM | 1997 | 459107400.942565 UNITED KINGDOM | 1996 | 469950800.228076 UNITED KINGDOM | 1995 | 461521940.646383 UNITED KINGDOM | 1994 | 459173657.653794 UNITED KINGDOM | 1993 | 464265817.703405 UNITED KINGDOM | 1992 | 463875402.123083 UNITED STATES | 1998 | 269616867.135055 UNITED STATES | 1997 | 464924613.80897 UNITED STATES | 1996 | 465124923.896053 UNITED STATES | 1995 | 462787204.650733 UNITED STATES | 1994 | 469158244.831926 UNITED STATES | 1993 | 463523691.886931 UNITED STATES | 1992 | 469119889.959495 VIETNAM | 1998 | 275529832.401166 VIETNAM | 1997 | 466309174.079998 VIETNAM | 1996 | 467415378.14928 VIETNAM | 1995 | 461201676.370995 VIETNAM | 1994 | 462886451.082266 VIETNAM | 1993 | 470946084.39615 VIETNAM | 1992 | 473175303.20206 (175 rows) COMMIT; COMMIT