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 '%ghost%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272527587.927231 ALGERIA | 1997 | 460169100.529781 ALGERIA | 1996 | 467273719.26463 ALGERIA | 1995 | 466574305.046528 ALGERIA | 1994 | 457665366.939433 ALGERIA | 1993 | 470602653.252345 ALGERIA | 1992 | 464673522.469625 ARGENTINA | 1998 | 276417028.334091 ARGENTINA | 1997 | 471747939.281387 ARGENTINA | 1996 | 475760805.571986 ARGENTINA | 1995 | 471637683.403224 ARGENTINA | 1994 | 466731563.806448 ARGENTINA | 1993 | 466550011.630432 ARGENTINA | 1992 | 469398071.394884 BRAZIL | 1998 | 271839436.690482 BRAZIL | 1997 | 464299731.95457 BRAZIL | 1996 | 468709359.836767 BRAZIL | 1995 | 470709634.974745 BRAZIL | 1994 | 463880880.151765 BRAZIL | 1993 | 463149985.037705 BRAZIL | 1992 | 471871034.432391 CANADA | 1998 | 274151793.742168 CANADA | 1997 | 478500671.950299 CANADA | 1996 | 472706663.809841 CANADA | 1995 | 478014807.202653 CANADA | 1994 | 480381058.831278 CANADA | 1993 | 483509816.85527 CANADA | 1992 | 484940678.062856 CHINA | 1998 | 280002706.539607 CHINA | 1997 | 468248525.78879 CHINA | 1996 | 477297211.397964 CHINA | 1995 | 474572537.62662 CHINA | 1994 | 471467411.338809 CHINA | 1993 | 477973292.01236 CHINA | 1992 | 476801468.499725 EGYPT | 1998 | 276007609.233334 EGYPT | 1997 | 458648091.997772 EGYPT | 1996 | 465002345.227801 EGYPT | 1995 | 465339444.955702 EGYPT | 1994 | 463099198.576993 EGYPT | 1993 | 460492282.861757 EGYPT | 1992 | 468768434.626067 ETHIOPIA | 1998 | 267236118.335288 ETHIOPIA | 1997 | 453631439.711482 ETHIOPIA | 1996 | 459225895.936222 ETHIOPIA | 1995 | 461092983.123051 ETHIOPIA | 1994 | 456672303.354241 ETHIOPIA | 1993 | 456555381.539626 ETHIOPIA | 1992 | 458293059.453729 FRANCE | 1998 | 271643286.139839 FRANCE | 1997 | 459835242.599442 FRANCE | 1996 | 460717598.168931 FRANCE | 1995 | 465124068.578254 FRANCE | 1994 | 456469843.237182 FRANCE | 1993 | 465449909.001737 FRANCE | 1992 | 459904536.259744 GERMANY | 1998 | 277807343.644388 GERMANY | 1997 | 476156544.53173 GERMANY | 1996 | 469076884.680522 GERMANY | 1995 | 470933337.408446 GERMANY | 1994 | 473832495.708498 GERMANY | 1993 | 466786603.174676 GERMANY | 1992 | 462099683.436966 INDIA | 1998 | 270413669.794025 INDIA | 1997 | 469207462.748109 INDIA | 1996 | 476815899.102774 INDIA | 1995 | 466954669.572705 INDIA | 1994 | 463890245.087864 INDIA | 1993 | 463967674.542569 INDIA | 1992 | 467014337.443834 INDONESIA | 1998 | 273412232.990516 INDONESIA | 1997 | 457126658.873955 INDONESIA | 1996 | 460133295.978707 INDONESIA | 1995 | 464987871.934046 INDONESIA | 1994 | 455056169.023135 INDONESIA | 1993 | 461524679.157072 INDONESIA | 1992 | 468827908.560412 IRAN | 1998 | 272760822.303391 IRAN | 1997 | 462105585.733801 IRAN | 1996 | 461825623.802952 IRAN | 1995 | 461034977.366142 IRAN | 1994 | 467863857.394601 IRAN | 1993 | 462557561.026744 IRAN | 1992 | 468962402.953968 IRAQ | 1998 | 280422950.736724 IRAQ | 1997 | 472467446.030697 IRAQ | 1996 | 480169858.337377 IRAQ | 1995 | 476945672.040448 IRAQ | 1994 | 481716254.202749 IRAQ | 1993 | 467344975.04309 IRAQ | 1992 | 478531264.736785 JAPAN | 1998 | 274902055.49636 JAPAN | 1997 | 468751107.507127 JAPAN | 1996 | 458596133.536648 JAPAN | 1995 | 461746838.046107 JAPAN | 1994 | 462841720.822418 JAPAN | 1993 | 462820135.234895 JAPAN | 1992 | 463182304.342676 JORDAN | 1998 | 274596251.77143 JORDAN | 1997 | 468802675.525666 JORDAN | 1996 | 460771303.970188 JORDAN | 1995 | 465038844.791503 JORDAN | 1994 | 458872100.488654 JORDAN | 1993 | 460736926.361602 JORDAN | 1992 | 459220718.933728 KENYA | 1998 | 272692654.608059 KENYA | 1997 | 471461973.427097 KENYA | 1996 | 474269372.000045 KENYA | 1995 | 473613063.707291 KENYA | 1994 | 469003208.997578 KENYA | 1993 | 479366831.606637 KENYA | 1992 | 473556030.957489 MOROCCO | 1998 | 278333674.246276 MOROCCO | 1997 | 475966436.943268 MOROCCO | 1996 | 477427262.510501 MOROCCO | 1995 | 475132273.137691 MOROCCO | 1994 | 473971751.387505 MOROCCO | 1993 | 473559613.29166 MOROCCO | 1992 | 468937188.127618 MOZAMBIQUE | 1998 | 270989692.403402 MOZAMBIQUE | 1997 | 453771396.592653 MOZAMBIQUE | 1996 | 459702923.311027 MOZAMBIQUE | 1995 | 462180050.134236 MOZAMBIQUE | 1994 | 459702698.868378 MOZAMBIQUE | 1993 | 459171904.10142 MOZAMBIQUE | 1992 | 451581905.913839 PERU | 1998 | 275338778.933391 PERU | 1997 | 464771027.492711 PERU | 1996 | 467627372.01124 PERU | 1995 | 470486947.365173 PERU | 1994 | 461547066.617813 PERU | 1993 | 463469903.874324 PERU | 1992 | 466726246.03247 ROMANIA | 1998 | 276969417.00523 ROMANIA | 1997 | 469803028.477566 ROMANIA | 1996 | 471364254.833327 ROMANIA | 1995 | 476206599.399992 ROMANIA | 1994 | 469840957.521174 ROMANIA | 1993 | 472552162.125599 ROMANIA | 1992 | 475175931.648339 RUSSIA | 1998 | 274817185.956789 RUSSIA | 1997 | 470938652.275452 RUSSIA | 1996 | 473702971.443885 RUSSIA | 1995 | 462695609.20923 RUSSIA | 1994 | 475997448.886328 RUSSIA | 1993 | 471048197.498932 RUSSIA | 1992 | 471429705.77531 SAUDI ARABIA | 1998 | 271788090.171833 SAUDI ARABIA | 1997 | 483718734.749244 SAUDI ARABIA | 1996 | 472962658.626334 SAUDI ARABIA | 1995 | 475313164.290528 SAUDI ARABIA | 1994 | 474147444.407356 SAUDI ARABIA | 1993 | 464663755.342377 SAUDI ARABIA | 1992 | 472218608.476132 UNITED KINGDOM | 1998 | 267407477.708093 UNITED KINGDOM | 1997 | 459236637.898344 UNITED KINGDOM | 1996 | 462888316.38942 UNITED KINGDOM | 1995 | 450083286.054403 UNITED KINGDOM | 1994 | 457310038.884334 UNITED KINGDOM | 1993 | 448418539.304176 UNITED KINGDOM | 1992 | 461135433.490066 UNITED STATES | 1998 | 271061096.749003 UNITED STATES | 1997 | 464628182.806644 UNITED STATES | 1996 | 464392377.090203 UNITED STATES | 1995 | 461166983.857638 UNITED STATES | 1994 | 473683295.270294 UNITED STATES | 1993 | 471527203.267397 UNITED STATES | 1992 | 462092334.924609 VIETNAM | 1998 | 275028594.142851 VIETNAM | 1997 | 468922533.734816 VIETNAM | 1996 | 484352318.83227 VIETNAM | 1995 | 471821812.3885 VIETNAM | 1994 | 478146419.706109 VIETNAM | 1993 | 468396818.667557 VIETNAM | 1992 | 470330376.597214 (175 rows) COMMIT; COMMIT