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 '%medium%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265764335.046579 ALGERIA | 1997 | 463259741.550659 ALGERIA | 1996 | 459482090.685911 ALGERIA | 1995 | 466444354.495278 ALGERIA | 1994 | 461853201.395026 ALGERIA | 1993 | 467875208.684973 ALGERIA | 1992 | 466645229.691951 ARGENTINA | 1998 | 275310919.435644 ARGENTINA | 1997 | 465911870.065248 ARGENTINA | 1996 | 471824419.952597 ARGENTINA | 1995 | 469146751.549355 ARGENTINA | 1994 | 470070593.34534 ARGENTINA | 1993 | 473658796.183924 ARGENTINA | 1992 | 470246491.918577 BRAZIL | 1998 | 272773086.738217 BRAZIL | 1997 | 462877453.584382 BRAZIL | 1996 | 475608396.053421 BRAZIL | 1995 | 468445985.120348 BRAZIL | 1994 | 461276439.008979 BRAZIL | 1993 | 462961042.837405 BRAZIL | 1992 | 471276861.776393 CANADA | 1998 | 273649715.994569 CANADA | 1997 | 476562947.064447 CANADA | 1996 | 476698395.329718 CANADA | 1995 | 476376700.276214 CANADA | 1994 | 476775540.856453 CANADA | 1993 | 474168109.027086 CANADA | 1992 | 479305097.601106 CHINA | 1998 | 269211499.80367 CHINA | 1997 | 462919706.851663 CHINA | 1996 | 466188703.130854 CHINA | 1995 | 463972345.452936 CHINA | 1994 | 465910723.74805 CHINA | 1993 | 466968202.295102 CHINA | 1992 | 470088126.634393 EGYPT | 1998 | 270371964.216791 EGYPT | 1997 | 465347860.451613 EGYPT | 1996 | 464902527.61542 EGYPT | 1995 | 457937586.341071 EGYPT | 1994 | 458291107.932373 EGYPT | 1993 | 469686193.202969 EGYPT | 1992 | 462714571.089314 ETHIOPIA | 1998 | 264707244.527539 ETHIOPIA | 1997 | 463818087.858989 ETHIOPIA | 1996 | 465456804.95789 ETHIOPIA | 1995 | 465675970.959351 ETHIOPIA | 1994 | 462058068.502325 ETHIOPIA | 1993 | 458567412.502648 ETHIOPIA | 1992 | 469147857.164642 FRANCE | 1998 | 263731621.104596 FRANCE | 1997 | 439464100.635913 FRANCE | 1996 | 457873086.298635 FRANCE | 1995 | 449657009.054203 FRANCE | 1994 | 453392214.044914 FRANCE | 1993 | 459324539.15561 FRANCE | 1992 | 448357441.210163 GERMANY | 1998 | 277742882.950143 GERMANY | 1997 | 477688045.315261 GERMANY | 1996 | 473508068.076963 GERMANY | 1995 | 475128634.046072 GERMANY | 1994 | 474008426.656252 GERMANY | 1993 | 482662673.151691 GERMANY | 1992 | 473741567.269125 INDIA | 1998 | 284369028.708153 INDIA | 1997 | 482788831.630237 INDIA | 1996 | 485528604.484862 INDIA | 1995 | 481814410.268231 INDIA | 1994 | 490958979.154516 INDIA | 1993 | 480343732.081948 INDIA | 1992 | 485438377.164575 INDONESIA | 1998 | 272534958.16512 INDONESIA | 1997 | 463386532.446993 INDONESIA | 1996 | 471357205.458002 INDONESIA | 1995 | 466464950.537757 INDONESIA | 1994 | 460587745.829373 INDONESIA | 1993 | 473195525.275997 INDONESIA | 1992 | 463495338.736115 IRAN | 1998 | 270774390.284574 IRAN | 1997 | 456244127.095445 IRAN | 1996 | 454829706.966356 IRAN | 1995 | 457478662.713164 IRAN | 1994 | 456967395.424034 IRAN | 1993 | 460365352.130882 IRAN | 1992 | 458439081.507565 IRAQ | 1998 | 276815860.248913 IRAQ | 1997 | 474070115.16133 IRAQ | 1996 | 472798459.60554 IRAQ | 1995 | 474564227.908728 IRAQ | 1994 | 466401682.657231 IRAQ | 1993 | 473348767.989435 IRAQ | 1992 | 467872040.969352 JAPAN | 1998 | 274495362.415467 JAPAN | 1997 | 461544551.908873 JAPAN | 1996 | 471800620.120808 JAPAN | 1995 | 460760778.528954 JAPAN | 1994 | 476168797.494255 JAPAN | 1993 | 466577356.678487 JAPAN | 1992 | 468422638.381687 JORDAN | 1998 | 270101862.493744 JORDAN | 1997 | 460191476.591074 JORDAN | 1996 | 460802965.835791 JORDAN | 1995 | 459934863.671589 JORDAN | 1994 | 457225364.422028 JORDAN | 1993 | 465113388.923755 JORDAN | 1992 | 465253729.309513 KENYA | 1998 | 275531156.070381 KENYA | 1997 | 471246533.896686 KENYA | 1996 | 473393525.813115 KENYA | 1995 | 473501758.975199 KENYA | 1994 | 463809845.190502 KENYA | 1993 | 467887067.376233 KENYA | 1992 | 470465788.466775 MOROCCO | 1998 | 275472852.123217 MOROCCO | 1997 | 466717558.325204 MOROCCO | 1996 | 466785589.586602 MOROCCO | 1995 | 476452736.035927 MOROCCO | 1994 | 470305512.911387 MOROCCO | 1993 | 473678206.234629 MOROCCO | 1992 | 468329941.321124 MOZAMBIQUE | 1998 | 271560724.154162 MOZAMBIQUE | 1997 | 464737112.042813 MOZAMBIQUE | 1996 | 466886767.905875 MOZAMBIQUE | 1995 | 465973047.340889 MOZAMBIQUE | 1994 | 456224751.8107 MOZAMBIQUE | 1993 | 459819230.553103 MOZAMBIQUE | 1992 | 455503771.53681 PERU | 1998 | 274493044.897896 PERU | 1997 | 468190622.14384 PERU | 1996 | 464198645.246479 PERU | 1995 | 464356388.660422 PERU | 1994 | 463617728.454207 PERU | 1993 | 459945866.904256 PERU | 1992 | 469207494.50932 ROMANIA | 1998 | 267258231.207104 ROMANIA | 1997 | 461908727.296919 ROMANIA | 1996 | 467480204.833925 ROMANIA | 1995 | 465232334.914667 ROMANIA | 1994 | 467928033.223169 ROMANIA | 1993 | 462118611.585722 ROMANIA | 1992 | 468228041.060776 RUSSIA | 1998 | 269169081.201474 RUSSIA | 1997 | 467664425.315396 RUSSIA | 1996 | 469072517.270575 RUSSIA | 1995 | 470263960.506832 RUSSIA | 1994 | 469193837.031631 RUSSIA | 1993 | 460826089.893945 RUSSIA | 1992 | 467605462.735353 SAUDI ARABIA | 1998 | 273031933.365391 SAUDI ARABIA | 1997 | 465775052.035595 SAUDI ARABIA | 1996 | 464581089.313524 SAUDI ARABIA | 1995 | 471186245.683639 SAUDI ARABIA | 1994 | 460497040.899049 SAUDI ARABIA | 1993 | 469508718.391559 SAUDI ARABIA | 1992 | 467074690.091131 UNITED KINGDOM | 1998 | 266509302.450339 UNITED KINGDOM | 1997 | 469963351.229304 UNITED KINGDOM | 1996 | 464859169.020974 UNITED KINGDOM | 1995 | 454432093.258531 UNITED KINGDOM | 1994 | 458358281.697314 UNITED KINGDOM | 1993 | 466876705.719856 UNITED KINGDOM | 1992 | 458267285.954717 UNITED STATES | 1998 | 279699509.043924 UNITED STATES | 1997 | 476305529.534505 UNITED STATES | 1996 | 479446323.150961 UNITED STATES | 1995 | 470524600.968699 UNITED STATES | 1994 | 477048288.820809 UNITED STATES | 1993 | 479472812.423005 UNITED STATES | 1992 | 467638223.499354 VIETNAM | 1998 | 269083197.021474 VIETNAM | 1997 | 463295061.193431 VIETNAM | 1996 | 474067744.277866 VIETNAM | 1995 | 462863918.325952 VIETNAM | 1994 | 470680411.354562 VIETNAM | 1993 | 468135624.664373 VIETNAM | 1992 | 474447705.484713 (175 rows) COMMIT; COMMIT