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 '%navajo%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272204292.796258 ALGERIA | 1997 | 458797836.155566 ALGERIA | 1996 | 460504909.322707 ALGERIA | 1995 | 463414048.948724 ALGERIA | 1994 | 451965621.332531 ALGERIA | 1993 | 464311654.141864 ALGERIA | 1992 | 462989459.601991 ARGENTINA | 1998 | 281457797.401089 ARGENTINA | 1997 | 474004476.936089 ARGENTINA | 1996 | 474874510.305923 ARGENTINA | 1995 | 473114198.129315 ARGENTINA | 1994 | 476497485.571038 ARGENTINA | 1993 | 476998903.59258 ARGENTINA | 1992 | 474887180.984076 BRAZIL | 1998 | 265678010.713954 BRAZIL | 1997 | 461380204.820642 BRAZIL | 1996 | 464858986.495737 BRAZIL | 1995 | 467558055.546582 BRAZIL | 1994 | 469785115.651603 BRAZIL | 1993 | 470467765.899546 BRAZIL | 1992 | 468572105.805941 CANADA | 1998 | 284668810.820398 CANADA | 1997 | 489604205.800603 CANADA | 1996 | 482504627.877087 CANADA | 1995 | 491181102.890463 CANADA | 1994 | 487342698.306885 CANADA | 1993 | 485352345.943637 CANADA | 1992 | 492317707.092206 CHINA | 1998 | 281973767.963938 CHINA | 1997 | 470521375.559715 CHINA | 1996 | 467386973.786052 CHINA | 1995 | 470932173.279309 CHINA | 1994 | 474712502.5769 CHINA | 1993 | 469714609.812582 CHINA | 1992 | 476362627.87431 EGYPT | 1998 | 271409230.043106 EGYPT | 1997 | 462185263.312649 EGYPT | 1996 | 469008592.157451 EGYPT | 1995 | 460950590.344062 EGYPT | 1994 | 466240608.762066 EGYPT | 1993 | 465383441.798189 EGYPT | 1992 | 463912230.963811 ETHIOPIA | 1998 | 277489855.273229 ETHIOPIA | 1997 | 459786910.796747 ETHIOPIA | 1996 | 458664956.88543 ETHIOPIA | 1995 | 459725980.966726 ETHIOPIA | 1994 | 462738645.438973 ETHIOPIA | 1993 | 469802120.070535 ETHIOPIA | 1992 | 460610691.845843 FRANCE | 1998 | 267708146.701777 FRANCE | 1997 | 456776839.687024 FRANCE | 1996 | 461372401.743884 FRANCE | 1995 | 455635798.21113 FRANCE | 1994 | 461161602.930232 FRANCE | 1993 | 459408991.647646 FRANCE | 1992 | 450542354.503449 GERMANY | 1998 | 279769102.47746 GERMANY | 1997 | 469254651.460507 GERMANY | 1996 | 463663344.868896 GERMANY | 1995 | 468234408.662521 GERMANY | 1994 | 469257039.609188 GERMANY | 1993 | 468035275.966308 GERMANY | 1992 | 470434658.221425 INDIA | 1998 | 278442728.955836 INDIA | 1997 | 483567099.872908 INDIA | 1996 | 486408012.735265 INDIA | 1995 | 478284720.258785 INDIA | 1994 | 480967449.537649 INDIA | 1993 | 486784806.02972 INDIA | 1992 | 480095859.403137 INDONESIA | 1998 | 270602566.692815 INDONESIA | 1997 | 460026413.326078 INDONESIA | 1996 | 473822704.898996 INDONESIA | 1995 | 464788661.179673 INDONESIA | 1994 | 459495249.981097 INDONESIA | 1993 | 469985520.764358 INDONESIA | 1992 | 465376492.655492 IRAN | 1998 | 272912675.605013 IRAN | 1997 | 466368990.106003 IRAN | 1996 | 470725048.4616 IRAN | 1995 | 467607907.269308 IRAN | 1994 | 465624639.911028 IRAN | 1993 | 471728235.674796 IRAN | 1992 | 474520842.493313 IRAQ | 1998 | 280232733.613119 IRAQ | 1997 | 480909061.945616 IRAQ | 1996 | 478830653.449656 IRAQ | 1995 | 479529674.372324 IRAQ | 1994 | 482658862.155346 IRAQ | 1993 | 472132800.541726 IRAQ | 1992 | 480044051.966461 JAPAN | 1998 | 276154397.017014 JAPAN | 1997 | 477820364.521569 JAPAN | 1996 | 476153203.791498 JAPAN | 1995 | 471587491.723073 JAPAN | 1994 | 468068253.666622 JAPAN | 1993 | 469211869.40472 JAPAN | 1992 | 472191631.560369 JORDAN | 1998 | 271902640.085136 JORDAN | 1997 | 465696505.653127 JORDAN | 1996 | 465467500.157541 JORDAN | 1995 | 462803916.240403 JORDAN | 1994 | 456962989.311712 JORDAN | 1993 | 464154092.485433 JORDAN | 1992 | 473944692.727803 KENYA | 1998 | 280331796.44652 KENYA | 1997 | 476752517.65205 KENYA | 1996 | 472408448.959674 KENYA | 1995 | 468753005.505742 KENYA | 1994 | 472239268.971824 KENYA | 1993 | 471659251.963129 KENYA | 1992 | 467963662.537111 MOROCCO | 1998 | 277785847.163673 MOROCCO | 1997 | 473570194.108992 MOROCCO | 1996 | 482208208.603409 MOROCCO | 1995 | 473141501.415735 MOROCCO | 1994 | 469401186.240616 MOROCCO | 1993 | 471358154.265014 MOROCCO | 1992 | 477241787.721252 MOZAMBIQUE | 1998 | 271148645.13452 MOZAMBIQUE | 1997 | 458626177.534228 MOZAMBIQUE | 1996 | 466352874.511712 MOZAMBIQUE | 1995 | 471467050.664574 MOZAMBIQUE | 1994 | 460900279.467305 MOZAMBIQUE | 1993 | 462277666.629804 MOZAMBIQUE | 1992 | 461594011.901702 PERU | 1998 | 281484692.231941 PERU | 1997 | 471566495.266491 PERU | 1996 | 472305573.29771 PERU | 1995 | 474748040.311318 PERU | 1994 | 470764972.486338 PERU | 1993 | 469064684.682429 PERU | 1992 | 472538664.896667 ROMANIA | 1998 | 282964807.356854 ROMANIA | 1997 | 472120189.653679 ROMANIA | 1996 | 478755689.812876 ROMANIA | 1995 | 473630375.40328 ROMANIA | 1994 | 477052003.624873 ROMANIA | 1993 | 475975963.568938 ROMANIA | 1992 | 474528860.701084 RUSSIA | 1998 | 279130775.719314 RUSSIA | 1997 | 477584372.047805 RUSSIA | 1996 | 482495915.622307 RUSSIA | 1995 | 485810231.659918 RUSSIA | 1994 | 482747049.712653 RUSSIA | 1993 | 479271722.317895 RUSSIA | 1992 | 482428580.669827 SAUDI ARABIA | 1998 | 282316867.676341 SAUDI ARABIA | 1997 | 465147252.24526 SAUDI ARABIA | 1996 | 474063352.139957 SAUDI ARABIA | 1995 | 472188353.936198 SAUDI ARABIA | 1994 | 471074875.782164 SAUDI ARABIA | 1993 | 465483552.237761 SAUDI ARABIA | 1992 | 461894197.261409 UNITED KINGDOM | 1998 | 265043121.436828 UNITED KINGDOM | 1997 | 467155614.378635 UNITED KINGDOM | 1996 | 456113832.817503 UNITED KINGDOM | 1995 | 460911391.179545 UNITED KINGDOM | 1994 | 456068106.515939 UNITED KINGDOM | 1993 | 450876311.038023 UNITED KINGDOM | 1992 | 454448234.421163 UNITED STATES | 1998 | 280133930.960646 UNITED STATES | 1997 | 473633263.208066 UNITED STATES | 1996 | 479378902.203394 UNITED STATES | 1995 | 473204076.189375 UNITED STATES | 1994 | 479639340.714169 UNITED STATES | 1993 | 476269024.35706 UNITED STATES | 1992 | 475509634.86438 VIETNAM | 1998 | 266710632.944175 VIETNAM | 1997 | 458857506.888429 VIETNAM | 1996 | 473663597.45577 VIETNAM | 1995 | 465409211.606221 VIETNAM | 1994 | 468735416.030935 VIETNAM | 1993 | 463403036.038383 VIETNAM | 1992 | 460602214.041665 (175 rows) COMMIT; COMMIT