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 '%lawn%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269381177.360793 ALGERIA | 1997 | 456109289.99155 ALGERIA | 1996 | 455298825.874461 ALGERIA | 1995 | 459790371.976723 ALGERIA | 1994 | 455195623.488912 ALGERIA | 1993 | 449688870.732391 ALGERIA | 1992 | 459079093.78878 ARGENTINA | 1998 | 276830129.21075 ARGENTINA | 1997 | 472853554.522712 ARGENTINA | 1996 | 467009750.255941 ARGENTINA | 1995 | 472951097.457774 ARGENTINA | 1994 | 471172441.663816 ARGENTINA | 1993 | 466410380.502641 ARGENTINA | 1992 | 469353089.200508 BRAZIL | 1998 | 271882775.147593 BRAZIL | 1997 | 468850108.456156 BRAZIL | 1996 | 465614177.945106 BRAZIL | 1995 | 464616109.635934 BRAZIL | 1994 | 471815902.134313 BRAZIL | 1993 | 466903571.452129 BRAZIL | 1992 | 474040670.189068 CANADA | 1998 | 278075992.041116 CANADA | 1997 | 472507204.632363 CANADA | 1996 | 469315874.87166 CANADA | 1995 | 476526595.32822 CANADA | 1994 | 483972880.074945 CANADA | 1993 | 472880941.181035 CANADA | 1992 | 478150467.590626 CHINA | 1998 | 276102345.082208 CHINA | 1997 | 471243315.824469 CHINA | 1996 | 466815432.925516 CHINA | 1995 | 476963494.168701 CHINA | 1994 | 472305110.487997 CHINA | 1993 | 460587196.619283 CHINA | 1992 | 470333717.568713 EGYPT | 1998 | 272046772.005773 EGYPT | 1997 | 458414855.424842 EGYPT | 1996 | 462558844.003231 EGYPT | 1995 | 463719672.229898 EGYPT | 1994 | 460973669.98793 EGYPT | 1993 | 466165591.629307 EGYPT | 1992 | 463704145.43315 ETHIOPIA | 1998 | 263003543.376234 ETHIOPIA | 1997 | 442025946.649698 ETHIOPIA | 1996 | 456299746.601091 ETHIOPIA | 1995 | 459252405.171974 ETHIOPIA | 1994 | 458142086.315434 ETHIOPIA | 1993 | 451929155.421702 ETHIOPIA | 1992 | 462777369.718274 FRANCE | 1998 | 266870369.967148 FRANCE | 1997 | 457719520.553455 FRANCE | 1996 | 464316669.304171 FRANCE | 1995 | 454110166.656793 FRANCE | 1994 | 456261135.20956 FRANCE | 1993 | 458077885.229779 FRANCE | 1992 | 457892651.686486 GERMANY | 1998 | 281255266.390184 GERMANY | 1997 | 470695575.485103 GERMANY | 1996 | 477334425.745609 GERMANY | 1995 | 470045160.098963 GERMANY | 1994 | 472471026.625484 GERMANY | 1993 | 476533105.350249 GERMANY | 1992 | 468629485.272012 INDIA | 1998 | 275489049.759884 INDIA | 1997 | 483811301.228065 INDIA | 1996 | 475891478.9766 INDIA | 1995 | 481326242.077276 INDIA | 1994 | 477107276.453105 INDIA | 1993 | 481564486.663291 INDIA | 1992 | 477246157.547839 INDONESIA | 1998 | 274532118.697435 INDONESIA | 1997 | 466481689.867267 INDONESIA | 1996 | 468452902.231421 INDONESIA | 1995 | 465405821.716588 INDONESIA | 1994 | 459160848.82035 INDONESIA | 1993 | 458412905.61828 INDONESIA | 1992 | 465881794.348455 IRAN | 1998 | 273333701.291752 IRAN | 1997 | 460107121.201495 IRAN | 1996 | 469689296.940708 IRAN | 1995 | 464262409.101278 IRAN | 1994 | 467981683.995781 IRAN | 1993 | 468666145.919827 IRAN | 1992 | 463180195.79953 IRAQ | 1998 | 263237782.89638 IRAQ | 1997 | 461614154.552746 IRAQ | 1996 | 466139767.850316 IRAQ | 1995 | 462537369.673169 IRAQ | 1994 | 453333826.473118 IRAQ | 1993 | 464582380.672469 IRAQ | 1992 | 462310716.217325 JAPAN | 1998 | 271619252.037 JAPAN | 1997 | 470643382.299232 JAPAN | 1996 | 471421497.573495 JAPAN | 1995 | 462421194.796974 JAPAN | 1994 | 465974430.431823 JAPAN | 1993 | 465475753.269891 JAPAN | 1992 | 466802597.917638 JORDAN | 1998 | 269446273.310368 JORDAN | 1997 | 457042911.587197 JORDAN | 1996 | 455308360.440247 JORDAN | 1995 | 450756538.907135 JORDAN | 1994 | 455344884.245668 JORDAN | 1993 | 453927156.841116 JORDAN | 1992 | 455729647.522507 KENYA | 1998 | 275491606.030801 KENYA | 1997 | 477390580.067809 KENYA | 1996 | 477499816.602982 KENYA | 1995 | 478166579.023547 KENYA | 1994 | 473469241.966143 KENYA | 1993 | 483436627.371251 KENYA | 1992 | 470584625.8303 MOROCCO | 1998 | 271737439.997629 MOROCCO | 1997 | 466468155.270602 MOROCCO | 1996 | 464744926.925505 MOROCCO | 1995 | 471100403.260766 MOROCCO | 1994 | 468247143.120788 MOROCCO | 1993 | 473876505.85165 MOROCCO | 1992 | 460860238.247988 MOZAMBIQUE | 1998 | 270467185.398002 MOZAMBIQUE | 1997 | 458547908.052078 MOZAMBIQUE | 1996 | 461428695.413167 MOZAMBIQUE | 1995 | 463052835.772649 MOZAMBIQUE | 1994 | 459759760.243084 MOZAMBIQUE | 1993 | 460827972.941192 MOZAMBIQUE | 1992 | 461629245.45684 PERU | 1998 | 273856791.129847 PERU | 1997 | 471603148.04903 PERU | 1996 | 469222001.449885 PERU | 1995 | 468792258.571564 PERU | 1994 | 466809020.017566 PERU | 1993 | 473652841.467167 PERU | 1992 | 466730407.435733 ROMANIA | 1998 | 278508918.262942 ROMANIA | 1997 | 470563712.522054 ROMANIA | 1996 | 479280027.869881 ROMANIA | 1995 | 473211027.372123 ROMANIA | 1994 | 463030154.0967 ROMANIA | 1993 | 473092283.529797 ROMANIA | 1992 | 474026997.494307 RUSSIA | 1998 | 272569459.553875 RUSSIA | 1997 | 467130616.011337 RUSSIA | 1996 | 473673741.736107 RUSSIA | 1995 | 463777041.332992 RUSSIA | 1994 | 474015756.698073 RUSSIA | 1993 | 466696693.289575 RUSSIA | 1992 | 466092822.858393 SAUDI ARABIA | 1998 | 277199168.941675 SAUDI ARABIA | 1997 | 472350255.380666 SAUDI ARABIA | 1996 | 482048358.639933 SAUDI ARABIA | 1995 | 472856110.231913 SAUDI ARABIA | 1994 | 470233483.737567 SAUDI ARABIA | 1993 | 477873580.468139 SAUDI ARABIA | 1992 | 480497356.624394 UNITED KINGDOM | 1998 | 269733265.55298 UNITED KINGDOM | 1997 | 459444600.758312 UNITED KINGDOM | 1996 | 460896856.885462 UNITED KINGDOM | 1995 | 454945075.72322 UNITED KINGDOM | 1994 | 452295540.990184 UNITED KINGDOM | 1993 | 464897212.134903 UNITED KINGDOM | 1992 | 456354467.962292 UNITED STATES | 1998 | 275556373.646859 UNITED STATES | 1997 | 475522639.571583 UNITED STATES | 1996 | 471767944.569497 UNITED STATES | 1995 | 480534944.762433 UNITED STATES | 1994 | 473367353.658383 UNITED STATES | 1993 | 481434750.397868 UNITED STATES | 1992 | 473653075.130544 VIETNAM | 1998 | 275492041.447747 VIETNAM | 1997 | 462926876.217723 VIETNAM | 1996 | 469294476.981306 VIETNAM | 1995 | 462511228.468838 VIETNAM | 1994 | 469000752.127872 VIETNAM | 1993 | 462117210.802216 VIETNAM | 1992 | 471246956.760026 (175 rows) COMMIT; COMMIT