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 '%moccasin%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270242660.770367 ALGERIA | 1997 | 457503463.788719 ALGERIA | 1996 | 461082664.858184 ALGERIA | 1995 | 459720204.233927 ALGERIA | 1994 | 457687096.955292 ALGERIA | 1993 | 459277683.927656 ALGERIA | 1992 | 453642207.95682 ARGENTINA | 1998 | 274923876.922208 ARGENTINA | 1997 | 467311559.283978 ARGENTINA | 1996 | 465365870.495971 ARGENTINA | 1995 | 467813918.499967 ARGENTINA | 1994 | 466839298.057075 ARGENTINA | 1993 | 465045008.14198 ARGENTINA | 1992 | 472835886.728046 BRAZIL | 1998 | 273125910.854284 BRAZIL | 1997 | 470974550.338558 BRAZIL | 1996 | 467529904.605292 BRAZIL | 1995 | 469012187.551445 BRAZIL | 1994 | 470386523.560316 BRAZIL | 1993 | 465977457.36175 BRAZIL | 1992 | 471833944.564816 CANADA | 1998 | 278802551.247046 CANADA | 1997 | 480771400.611715 CANADA | 1996 | 477860186.399436 CANADA | 1995 | 476837821.977334 CANADA | 1994 | 470601061.687455 CANADA | 1993 | 469094475.987899 CANADA | 1992 | 478351285.570985 CHINA | 1998 | 277734519.960175 CHINA | 1997 | 464815760.60698 CHINA | 1996 | 458516235.536297 CHINA | 1995 | 471786798.096615 CHINA | 1994 | 467200701.666511 CHINA | 1993 | 470176952.019777 CHINA | 1992 | 462040157.143569 EGYPT | 1998 | 268674672.309096 EGYPT | 1997 | 458961994.085503 EGYPT | 1996 | 453178481.854796 EGYPT | 1995 | 461583487.765213 EGYPT | 1994 | 455778432.738479 EGYPT | 1993 | 457018017.915416 EGYPT | 1992 | 457359542.812339 ETHIOPIA | 1998 | 272595055.805369 ETHIOPIA | 1997 | 457811602.71599 ETHIOPIA | 1996 | 464325173.679211 ETHIOPIA | 1995 | 461309319.698954 ETHIOPIA | 1994 | 463579618.553226 ETHIOPIA | 1993 | 459490558.953054 ETHIOPIA | 1992 | 463124050.87496 FRANCE | 1998 | 264197513.666585 FRANCE | 1997 | 453485708.549069 FRANCE | 1996 | 463325351.353708 FRANCE | 1995 | 459729702.637765 FRANCE | 1994 | 457175718.859097 FRANCE | 1993 | 459718798.987843 FRANCE | 1992 | 450408967.782359 GERMANY | 1998 | 275065683.944151 GERMANY | 1997 | 466608428.356691 GERMANY | 1996 | 474532326.332219 GERMANY | 1995 | 469119457.33954 GERMANY | 1994 | 471088466.144345 GERMANY | 1993 | 477530298.309491 GERMANY | 1992 | 473620715.091019 INDIA | 1998 | 279722811.461683 INDIA | 1997 | 478778314.640854 INDIA | 1996 | 485645883.447279 INDIA | 1995 | 485113232.741212 INDIA | 1994 | 485684273.259748 INDIA | 1993 | 485640179.326709 INDIA | 1992 | 482655254.705264 INDONESIA | 1998 | 265504761.600161 INDONESIA | 1997 | 458222915.881763 INDONESIA | 1996 | 459840889.800797 INDONESIA | 1995 | 456501904.383119 INDONESIA | 1994 | 459564922.707792 INDONESIA | 1993 | 468606634.228827 INDONESIA | 1992 | 461471104.519617 IRAN | 1998 | 270703408.758924 IRAN | 1997 | 461463789.228438 IRAN | 1996 | 465955214.609037 IRAN | 1995 | 464596857.704097 IRAN | 1994 | 467783332.229147 IRAN | 1993 | 463028472.508578 IRAN | 1992 | 463984474.40706 IRAQ | 1998 | 277950312.703597 IRAQ | 1997 | 488474638.494313 IRAQ | 1996 | 480777282.621659 IRAQ | 1995 | 480792373.626012 IRAQ | 1994 | 482637774.536818 IRAQ | 1993 | 487442372.552092 IRAQ | 1992 | 478187027.518632 JAPAN | 1998 | 270273154.943368 JAPAN | 1997 | 470062875.791754 JAPAN | 1996 | 461566265.497868 JAPAN | 1995 | 458496244.891581 JAPAN | 1994 | 463616726.288634 JAPAN | 1993 | 465434694.720748 JAPAN | 1992 | 459150631.860982 JORDAN | 1998 | 270454370.690354 JORDAN | 1997 | 457490325.941634 JORDAN | 1996 | 461536535.226101 JORDAN | 1995 | 451618474.284319 JORDAN | 1994 | 461461513.029782 JORDAN | 1993 | 457741403.185424 JORDAN | 1992 | 460706464.732735 KENYA | 1998 | 279981942.914432 KENYA | 1997 | 471069968.036124 KENYA | 1996 | 471654572.472582 KENYA | 1995 | 475714422.40198 KENYA | 1994 | 475121895.591524 KENYA | 1993 | 468703501.732702 KENYA | 1992 | 465465015.294928 MOROCCO | 1998 | 266502868.22223 MOROCCO | 1997 | 458377798.472615 MOROCCO | 1996 | 457907985.229236 MOROCCO | 1995 | 458318301.196574 MOROCCO | 1994 | 455543016.632264 MOROCCO | 1993 | 458828937.176835 MOROCCO | 1992 | 454824024.562891 MOZAMBIQUE | 1998 | 273365709.2066 MOZAMBIQUE | 1997 | 460442141.717957 MOZAMBIQUE | 1996 | 465830443.275329 MOZAMBIQUE | 1995 | 462403575.866103 MOZAMBIQUE | 1994 | 468197888.492665 MOZAMBIQUE | 1993 | 468139691.193479 MOZAMBIQUE | 1992 | 468068827.327472 PERU | 1998 | 276949401.263681 PERU | 1997 | 472470528.254932 PERU | 1996 | 470822892.161378 PERU | 1995 | 463739089.453245 PERU | 1994 | 469116913.425983 PERU | 1993 | 463377744.350608 PERU | 1992 | 476516671.806849 ROMANIA | 1998 | 275190514.784322 ROMANIA | 1997 | 463670917.356008 ROMANIA | 1996 | 472258227.047311 ROMANIA | 1995 | 466504128.641061 ROMANIA | 1994 | 462611229.6641 ROMANIA | 1993 | 464370434.127869 ROMANIA | 1992 | 469265068.123625 RUSSIA | 1998 | 277130536.095729 RUSSIA | 1997 | 474251092.116011 RUSSIA | 1996 | 484964457.143359 RUSSIA | 1995 | 471917428.891094 RUSSIA | 1994 | 469889746.681322 RUSSIA | 1993 | 477593137.862143 RUSSIA | 1992 | 478846156.939081 SAUDI ARABIA | 1998 | 281987487.255248 SAUDI ARABIA | 1997 | 474332177.798813 SAUDI ARABIA | 1996 | 474451235.470851 SAUDI ARABIA | 1995 | 476644198.311119 SAUDI ARABIA | 1994 | 479471351.772825 SAUDI ARABIA | 1993 | 473169026.956489 SAUDI ARABIA | 1992 | 478882740.039117 UNITED KINGDOM | 1998 | 271474083.179341 UNITED KINGDOM | 1997 | 466695078.192606 UNITED KINGDOM | 1996 | 467050117.866196 UNITED KINGDOM | 1995 | 472471689.519567 UNITED KINGDOM | 1994 | 463568352.443031 UNITED KINGDOM | 1993 | 460849814.809616 UNITED KINGDOM | 1992 | 463274755.60025 UNITED STATES | 1998 | 274801187.955494 UNITED STATES | 1997 | 470432146.976562 UNITED STATES | 1996 | 470214338.558505 UNITED STATES | 1995 | 455615288.257991 UNITED STATES | 1994 | 477455160.169204 UNITED STATES | 1993 | 475689757.278349 UNITED STATES | 1992 | 467666155.20319 VIETNAM | 1998 | 274001623.377615 VIETNAM | 1997 | 456120128.898908 VIETNAM | 1996 | 462667624.16809 VIETNAM | 1995 | 459059520.304671 VIETNAM | 1994 | 469156254.533177 VIETNAM | 1993 | 457352676.806525 VIETNAM | 1992 | 463101628.379855 (175 rows) COMMIT; COMMIT