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.77877 ALGERIA | 1996 | 464808428.208265 ALGERIA | 1995 | 465233151.668788 ALGERIA | 1994 | 464869791.1362 ALGERIA | 1993 | 464413495.150291 ALGERIA | 1992 | 464477456.928128 ARGENTINA | 1998 | 275566390.689581 ARGENTINA | 1997 | 471463847.581692 ARGENTINA | 1996 | 478014170.991456 ARGENTINA | 1995 | 473221013.022 ARGENTINA | 1994 | 468319858.263208 ARGENTINA | 1993 | 466918207.720299 ARGENTINA | 1992 | 473758836.846378 BRAZIL | 1998 | 279650243.342955 BRAZIL | 1997 | 456906779.912464 BRAZIL | 1996 | 463929655.795927 BRAZIL | 1995 | 459733119.072153 BRAZIL | 1994 | 467477257.306031 BRAZIL | 1993 | 461368022.112283 BRAZIL | 1992 | 468688169.890042 CANADA | 1998 | 270689661.113493 CANADA | 1997 | 483512826.062588 CANADA | 1996 | 478546617.787425 CANADA | 1995 | 476715268.341249 CANADA | 1994 | 480385124.728572 CANADA | 1993 | 474721905.343335 CANADA | 1992 | 468406836.466875 CHINA | 1998 | 267796124.211101 CHINA | 1997 | 453893714.772235 CHINA | 1996 | 464814458.958742 CHINA | 1995 | 462360753.567323 CHINA | 1994 | 454769783.380587 CHINA | 1993 | 455763954.892444 CHINA | 1992 | 460867930.769525 EGYPT | 1998 | 269640005.28468 EGYPT | 1997 | 467665924.392668 EGYPT | 1996 | 472228612.226794 EGYPT | 1995 | 459603100.900351 EGYPT | 1994 | 463255229.097702 EGYPT | 1993 | 470471340.448875 EGYPT | 1992 | 469589378.277588 ETHIOPIA | 1998 | 274646061.55699 ETHIOPIA | 1997 | 458825309.384598 ETHIOPIA | 1996 | 461504633.156464 ETHIOPIA | 1995 | 460792018.607678 ETHIOPIA | 1994 | 460373614.113207 ETHIOPIA | 1993 | 467565595.636837 ETHIOPIA | 1992 | 462574515.673983 FRANCE | 1998 | 270378720.135785 FRANCE | 1997 | 461779943.580372 FRANCE | 1996 | 461087362.268043 FRANCE | 1995 | 465813617.195051 FRANCE | 1994 | 464977240.98531 FRANCE | 1993 | 458336993.001659 FRANCE | 1992 | 462031322.778573 GERMANY | 1998 | 277692493.7273 GERMANY | 1997 | 476101144.665318 GERMANY | 1996 | 478895571.921388 GERMANY | 1995 | 484761742.339185 GERMANY | 1994 | 480638048.453484 GERMANY | 1993 | 488880685.358651 GERMANY | 1992 | 474329410.537333 INDIA | 1998 | 280002906.539342 INDIA | 1997 | 467309299.019151 INDIA | 1996 | 469380570.445878 INDIA | 1995 | 468983951.743924 INDIA | 1994 | 472807881.620856 INDIA | 1993 | 470093414.747092 INDIA | 1992 | 460521916.498379 INDONESIA | 1998 | 272265976.837126 INDONESIA | 1997 | 460972537.257112 INDONESIA | 1996 | 453833230.256151 INDONESIA | 1995 | 458574440.369379 INDONESIA | 1994 | 468480715.926526 INDONESIA | 1993 | 463861372.864648 INDONESIA | 1992 | 458833506.71701 IRAN | 1998 | 270886805.612101 IRAN | 1997 | 460025066.208352 IRAN | 1996 | 461016037.78211 IRAN | 1995 | 468372977.446744 IRAN | 1994 | 463873974.982463 IRAN | 1993 | 460356859.627596 IRAN | 1992 | 457798014.939955 IRAQ | 1998 | 278611381.875018 IRAQ | 1997 | 476757394.240267 IRAQ | 1996 | 480524051.484897 IRAQ | 1995 | 482607479.098 IRAQ | 1994 | 484122343.47437 IRAQ | 1993 | 471459292.088568 IRAQ | 1992 | 475582446.248825 JAPAN | 1998 | 276586915.460989 JAPAN | 1997 | 463507600.589664 JAPAN | 1996 | 472608986.265439 JAPAN | 1995 | 467360409.429416 JAPAN | 1994 | 467981866.841586 JAPAN | 1993 | 472631042.129565 JAPAN | 1992 | 473700028.923797 JORDAN | 1998 | 266762368.48743 JORDAN | 1997 | 454138000.489838 JORDAN | 1996 | 451625820.354197 JORDAN | 1995 | 455155076.999047 JORDAN | 1994 | 464023911.766973 JORDAN | 1993 | 457797345.236687 JORDAN | 1992 | 462867511.305729 KENYA | 1998 | 280774248.570986 KENYA | 1997 | 470536809.882898 KENYA | 1996 | 472273894.331693 KENYA | 1995 | 470356064.901986 KENYA | 1994 | 476387760.738266 KENYA | 1993 | 479086960.26194 KENYA | 1992 | 477923338.236179 MOROCCO | 1998 | 270907101.036275 MOROCCO | 1997 | 462571455.047423 MOROCCO | 1996 | 469542650.57088 MOROCCO | 1995 | 465740482.471238 MOROCCO | 1994 | 465709410.557074 MOROCCO | 1993 | 463067315.824051 MOROCCO | 1992 | 462013924.102624 MOZAMBIQUE | 1998 | 271089813.525369 MOZAMBIQUE | 1997 | 458466832.406262 MOZAMBIQUE | 1996 | 462163433.53984 MOZAMBIQUE | 1995 | 463625645.957531 MOZAMBIQUE | 1994 | 462868838.812133 MOZAMBIQUE | 1993 | 455074906.173793 MOZAMBIQUE | 1992 | 456596123.703204 PERU | 1998 | 276265122.018821 PERU | 1997 | 467018614.966338 PERU | 1996 | 459967291.133616 PERU | 1995 | 466964692.279264 PERU | 1994 | 455000364.192277 PERU | 1993 | 457934264.621455 PERU | 1992 | 466589942.145411 ROMANIA | 1998 | 273677998.511525 ROMANIA | 1997 | 471422072.843568 ROMANIA | 1996 | 472552309.895128 ROMANIA | 1995 | 467211561.086481 ROMANIA | 1994 | 462219104.346721 ROMANIA | 1993 | 473613753.884543 ROMANIA | 1992 | 477044537.068596 RUSSIA | 1998 | 276306863.704346 RUSSIA | 1997 | 470304801.483342 RUSSIA | 1996 | 484285561.680391 RUSSIA | 1995 | 476254617.698302 RUSSIA | 1994 | 474505177.48016 RUSSIA | 1993 | 475722150.374117 RUSSIA | 1992 | 480214262.141495 SAUDI ARABIA | 1998 | 269816724.617364 SAUDI ARABIA | 1997 | 460423990.992079 SAUDI ARABIA | 1996 | 465823040.390208 SAUDI ARABIA | 1995 | 460935774.277609 SAUDI ARABIA | 1994 | 459532240.110262 SAUDI ARABIA | 1993 | 462811617.96996 SAUDI ARABIA | 1992 | 468276388.41676 UNITED KINGDOM | 1998 | 270411517.709422 UNITED KINGDOM | 1997 | 463299731.413598 UNITED KINGDOM | 1996 | 456713760.7973 UNITED KINGDOM | 1995 | 461228822.767881 UNITED KINGDOM | 1994 | 458548737.330665 UNITED KINGDOM | 1993 | 461271561.137861 UNITED KINGDOM | 1992 | 464798591.215042 UNITED STATES | 1998 | 277445353.804802 UNITED STATES | 1997 | 476589655.319807 UNITED STATES | 1996 | 474476829.988908 UNITED STATES | 1995 | 476825126.208381 UNITED STATES | 1994 | 476289627.563946 UNITED STATES | 1993 | 472908449.296086 UNITED STATES | 1992 | 475506001.916612 VIETNAM | 1998 | 270805378.854866 VIETNAM | 1997 | 455024288.784777 VIETNAM | 1996 | 468419122.896707 VIETNAM | 1995 | 464858104.901261 VIETNAM | 1994 | 460322822.915598 VIETNAM | 1993 | 463481646.441238 VIETNAM | 1992 | 460868392.274471 (175 rows) COMMIT; COMMIT