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 '%chartreuse%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268361067.217655 ALGERIA | 1997 | 450703945.150954 ALGERIA | 1996 | 454543950.41597 ALGERIA | 1995 | 455866882.921313 ALGERIA | 1994 | 446782308.090807 ALGERIA | 1993 | 449547138.957972 ALGERIA | 1992 | 450714490.546906 ARGENTINA | 1998 | 278656493.746816 ARGENTINA | 1997 | 474810743.103769 ARGENTINA | 1996 | 477622253.170595 ARGENTINA | 1995 | 476016146.274741 ARGENTINA | 1994 | 472858916.614608 ARGENTINA | 1993 | 477933307.511684 ARGENTINA | 1992 | 472262598.557194 BRAZIL | 1998 | 269130435.202907 BRAZIL | 1997 | 461869611.779702 BRAZIL | 1996 | 462122161.065022 BRAZIL | 1995 | 454734806.093236 BRAZIL | 1994 | 458636162.800834 BRAZIL | 1993 | 453113726.200757 BRAZIL | 1992 | 461714942.195071 CANADA | 1998 | 274074657.827599 CANADA | 1997 | 469895412.761807 CANADA | 1996 | 470538718.707424 CANADA | 1995 | 465303278.608558 CANADA | 1994 | 465472682.440406 CANADA | 1993 | 466028968.926469 CANADA | 1992 | 469124060.35137 CHINA | 1998 | 278503826.476338 CHINA | 1997 | 467016443.298641 CHINA | 1996 | 470405233.344261 CHINA | 1995 | 457088493.395407 CHINA | 1994 | 463297306.586905 CHINA | 1993 | 461190838.589717 CHINA | 1992 | 462958226.624161 EGYPT | 1998 | 274594682.261452 EGYPT | 1997 | 456517887.346606 EGYPT | 1996 | 463168025.793321 EGYPT | 1995 | 465804182.826169 EGYPT | 1994 | 461541420.682935 EGYPT | 1993 | 466324701.395252 EGYPT | 1992 | 465346769.505296 ETHIOPIA | 1998 | 267282195.152808 ETHIOPIA | 1997 | 453996933.484459 ETHIOPIA | 1996 | 459867689.968196 ETHIOPIA | 1995 | 459284973.34423 ETHIOPIA | 1994 | 464799715.024159 ETHIOPIA | 1993 | 461512632.012371 ETHIOPIA | 1992 | 455080396.42826 FRANCE | 1998 | 271307052.853632 FRANCE | 1997 | 459416675.679192 FRANCE | 1996 | 460680586.136134 FRANCE | 1995 | 460187297.984262 FRANCE | 1994 | 457031518.512742 FRANCE | 1993 | 461307908.221441 FRANCE | 1992 | 454200238.152357 GERMANY | 1998 | 278556255.526028 GERMANY | 1997 | 470314393.777392 GERMANY | 1996 | 480891051.946203 GERMANY | 1995 | 475995275.193846 GERMANY | 1994 | 470516385.26833 GERMANY | 1993 | 473980207.28911 GERMANY | 1992 | 471056019.14288 INDIA | 1998 | 268918523.178766 INDIA | 1997 | 469672182.507428 INDIA | 1996 | 479294976.340184 INDIA | 1995 | 465919330.813242 INDIA | 1994 | 469215300.442749 INDIA | 1993 | 475265519.090581 INDIA | 1992 | 462025203.190128 INDONESIA | 1998 | 279235306.785888 INDONESIA | 1997 | 456654506.144653 INDONESIA | 1996 | 466809669.972797 INDONESIA | 1995 | 464764002.946217 INDONESIA | 1994 | 465870440.011048 INDONESIA | 1993 | 461649027.564778 INDONESIA | 1992 | 470377209.34575 IRAN | 1998 | 267021656.984475 IRAN | 1997 | 453258821.343821 IRAN | 1996 | 460472235.741578 IRAN | 1995 | 457357374.463001 IRAN | 1994 | 464760880.81677 IRAN | 1993 | 460549559.488427 IRAN | 1992 | 450271902.754277 IRAQ | 1998 | 280258187.466699 IRAQ | 1997 | 473511804.846962 IRAQ | 1996 | 470036189.070044 IRAQ | 1995 | 468098566.424305 IRAQ | 1994 | 465483708.210832 IRAQ | 1993 | 469736309.604139 IRAQ | 1992 | 477459526.735376 JAPAN | 1998 | 273768266.748958 JAPAN | 1997 | 470797027.156348 JAPAN | 1996 | 465863736.579051 JAPAN | 1995 | 463056598.044826 JAPAN | 1994 | 469387130.007069 JAPAN | 1993 | 465798608.063083 JAPAN | 1992 | 460296703.788967 JORDAN | 1998 | 269412936.857815 JORDAN | 1997 | 460181273.951046 JORDAN | 1996 | 458810377.270447 JORDAN | 1995 | 451240030.359289 JORDAN | 1994 | 454895700.473851 JORDAN | 1993 | 451601116.538843 JORDAN | 1992 | 456477316.86261 KENYA | 1998 | 271373987.571334 KENYA | 1997 | 463246542.340838 KENYA | 1996 | 470851602.802456 KENYA | 1995 | 476290442.154064 KENYA | 1994 | 465199998.528925 KENYA | 1993 | 463609109.13918 KENYA | 1992 | 475458805.282718 MOROCCO | 1998 | 281148808.245744 MOROCCO | 1997 | 468398326.451838 MOROCCO | 1996 | 468752208.639951 MOROCCO | 1995 | 464469503.329362 MOROCCO | 1994 | 468446357.612398 MOROCCO | 1993 | 469690411.653702 MOROCCO | 1992 | 469070548.026769 MOZAMBIQUE | 1998 | 275141756.910028 MOZAMBIQUE | 1997 | 469228497.882318 MOZAMBIQUE | 1996 | 466766953.141592 MOZAMBIQUE | 1995 | 472963510.386757 MOZAMBIQUE | 1994 | 467456347.387076 MOZAMBIQUE | 1993 | 467341803.032025 MOZAMBIQUE | 1992 | 467323214.448094 PERU | 1998 | 275276367.639719 PERU | 1997 | 464038509.173875 PERU | 1996 | 463349252.400103 PERU | 1995 | 460078590.821961 PERU | 1994 | 471384137.070945 PERU | 1993 | 461913668.80025 PERU | 1992 | 468133944.895716 ROMANIA | 1998 | 269048898.176319 ROMANIA | 1997 | 466061352.84601 ROMANIA | 1996 | 468349837.532586 ROMANIA | 1995 | 469352758.880452 ROMANIA | 1994 | 460692871.108552 ROMANIA | 1993 | 463853175.327019 ROMANIA | 1992 | 461851297.205582 RUSSIA | 1998 | 276868729.15268 RUSSIA | 1997 | 470685983.970752 RUSSIA | 1996 | 468007384.300346 RUSSIA | 1995 | 471257578.20347 RUSSIA | 1994 | 470041984.384361 RUSSIA | 1993 | 469270093.896819 RUSSIA | 1992 | 473869496.890653 SAUDI ARABIA | 1998 | 280475438.008356 SAUDI ARABIA | 1997 | 465429129.973778 SAUDI ARABIA | 1996 | 465411949.242396 SAUDI ARABIA | 1995 | 466954726.387519 SAUDI ARABIA | 1994 | 464452890.628689 SAUDI ARABIA | 1993 | 462964840.086201 SAUDI ARABIA | 1992 | 464358410.235881 UNITED KINGDOM | 1998 | 272374573.815895 UNITED KINGDOM | 1997 | 460320551.459088 UNITED KINGDOM | 1996 | 464799792.842701 UNITED KINGDOM | 1995 | 459700025.984817 UNITED KINGDOM | 1994 | 461953380.653429 UNITED KINGDOM | 1993 | 456156175.158165 UNITED KINGDOM | 1992 | 458737034.564795 UNITED STATES | 1998 | 270020208.929975 UNITED STATES | 1997 | 455683739.114804 UNITED STATES | 1996 | 459066384.904094 UNITED STATES | 1995 | 465903099.060733 UNITED STATES | 1994 | 470160634.883803 UNITED STATES | 1993 | 457271998.900658 UNITED STATES | 1992 | 454595755.115282 VIETNAM | 1998 | 274160229.596172 VIETNAM | 1997 | 460762707.54508 VIETNAM | 1996 | 468364211.845904 VIETNAM | 1995 | 464917949.98438 VIETNAM | 1994 | 466559300.746024 VIETNAM | 1993 | 464724167.072694 VIETNAM | 1992 | 462610174.749863 (175 rows) COMMIT; COMMIT