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 '%lavender%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267469956.567408 ALGERIA | 1997 | 454174094.742375 ALGERIA | 1996 | 464246621.232401 ALGERIA | 1995 | 463440705.043789 ALGERIA | 1994 | 464125884.949485 ALGERIA | 1993 | 465387402.760887 ALGERIA | 1992 | 461010049.915343 ARGENTINA | 1998 | 271224580.528349 ARGENTINA | 1997 | 473890162.00237 ARGENTINA | 1996 | 473257950.741083 ARGENTINA | 1995 | 473513585.395799 ARGENTINA | 1994 | 465254527.025471 ARGENTINA | 1993 | 467150349.606476 ARGENTINA | 1992 | 477609451.068185 BRAZIL | 1998 | 274803773.50669 BRAZIL | 1997 | 468387087.43791 BRAZIL | 1996 | 467953572.392964 BRAZIL | 1995 | 464099518.478449 BRAZIL | 1994 | 463721055.121342 BRAZIL | 1993 | 470429497.344471 BRAZIL | 1992 | 469034139.704453 CANADA | 1998 | 282785141.712302 CANADA | 1997 | 477424842.637434 CANADA | 1996 | 475557000.998358 CANADA | 1995 | 478602758.518378 CANADA | 1994 | 475675041.033412 CANADA | 1993 | 475633059.51099 CANADA | 1992 | 481350743.550102 CHINA | 1998 | 271779173.169501 CHINA | 1997 | 468991465.243209 CHINA | 1996 | 460762426.953215 CHINA | 1995 | 470090223.631378 CHINA | 1994 | 459807464.196403 CHINA | 1993 | 471204126.627882 CHINA | 1992 | 463549591.019554 EGYPT | 1998 | 274901503.407747 EGYPT | 1997 | 464745039.922224 EGYPT | 1996 | 469564437.679065 EGYPT | 1995 | 467058773.653046 EGYPT | 1994 | 467705165.135932 EGYPT | 1993 | 465543537.494738 EGYPT | 1992 | 460677208.06155 ETHIOPIA | 1998 | 268259899.446613 ETHIOPIA | 1997 | 453493613.209531 ETHIOPIA | 1996 | 459608011.728499 ETHIOPIA | 1995 | 467733550.226737 ETHIOPIA | 1994 | 463039369.053406 ETHIOPIA | 1993 | 455144809.528169 ETHIOPIA | 1992 | 458968609.188641 FRANCE | 1998 | 271409025.905493 FRANCE | 1997 | 449976995.026475 FRANCE | 1996 | 461352484.850596 FRANCE | 1995 | 464321474.381579 FRANCE | 1994 | 458801894.964275 FRANCE | 1993 | 458545778.333138 FRANCE | 1992 | 461452632.935257 GERMANY | 1998 | 277481715.768525 GERMANY | 1997 | 478106209.260829 GERMANY | 1996 | 478573961.661738 GERMANY | 1995 | 476447186.25812 GERMANY | 1994 | 483396454.053251 GERMANY | 1993 | 473786004.159453 GERMANY | 1992 | 481355551.168472 INDIA | 1998 | 273903277.382026 INDIA | 1997 | 466591084.151642 INDIA | 1996 | 478377703.560904 INDIA | 1995 | 463781930.335479 INDIA | 1994 | 467379653.975774 INDIA | 1993 | 473354616.206651 INDIA | 1992 | 471822428.647857 INDONESIA | 1998 | 279851749.403874 INDONESIA | 1997 | 475313764.223451 INDONESIA | 1996 | 470361897.756623 INDONESIA | 1995 | 473037280.969994 INDONESIA | 1994 | 475871853.332096 INDONESIA | 1993 | 470710008.075446 INDONESIA | 1992 | 478069374.666988 IRAN | 1998 | 264364465.094594 IRAN | 1997 | 462793024.858738 IRAN | 1996 | 461377574.913532 IRAN | 1995 | 466323518.470137 IRAN | 1994 | 461715593.999189 IRAN | 1993 | 456898517.516878 IRAN | 1992 | 462588198.545892 IRAQ | 1998 | 279573808.795211 IRAQ | 1997 | 472466640.00365 IRAQ | 1996 | 479958709.471641 IRAQ | 1995 | 473805012.004116 IRAQ | 1994 | 479079535.695953 IRAQ | 1993 | 473173452.967002 IRAQ | 1992 | 484789454.682359 JAPAN | 1998 | 273940997.649998 JAPAN | 1997 | 469369321.26012 JAPAN | 1996 | 465178468.405234 JAPAN | 1995 | 473307973.032057 JAPAN | 1994 | 461733580.349576 JAPAN | 1993 | 469047100.530344 JAPAN | 1992 | 468815922.35915 JORDAN | 1998 | 270387146.715051 JORDAN | 1997 | 462243129.162633 JORDAN | 1996 | 463108906.36957 JORDAN | 1995 | 464090991.709974 JORDAN | 1994 | 463084432.993497 JORDAN | 1993 | 469190790.964319 JORDAN | 1992 | 466801540.859637 KENYA | 1998 | 280739139.025128 KENYA | 1997 | 475459122.457466 KENYA | 1996 | 472249581.002722 KENYA | 1995 | 472700808.609718 KENYA | 1994 | 465586509.943351 KENYA | 1993 | 476984343.96481 KENYA | 1992 | 474430710.329087 MOROCCO | 1998 | 268498077.486925 MOROCCO | 1997 | 476064328.6737 MOROCCO | 1996 | 470238964.898657 MOROCCO | 1995 | 475019755.218201 MOROCCO | 1994 | 470788954.04463 MOROCCO | 1993 | 478298147.678155 MOROCCO | 1992 | 475065109.427971 MOZAMBIQUE | 1998 | 271586688.239691 MOZAMBIQUE | 1997 | 459253498.60936 MOZAMBIQUE | 1996 | 460440377.732264 MOZAMBIQUE | 1995 | 461789359.735884 MOZAMBIQUE | 1994 | 461070264.952096 MOZAMBIQUE | 1993 | 462284923.240358 MOZAMBIQUE | 1992 | 468207979.673523 PERU | 1998 | 272559410.815706 PERU | 1997 | 458331016.558361 PERU | 1996 | 473217776.263091 PERU | 1995 | 469644609.199718 PERU | 1994 | 467589870.799858 PERU | 1993 | 466353278.69112 PERU | 1992 | 463216936.717516 ROMANIA | 1998 | 279619934.239929 ROMANIA | 1997 | 473927627.350512 ROMANIA | 1996 | 482110744.138989 ROMANIA | 1995 | 481390968.55462 ROMANIA | 1994 | 477991403.173092 ROMANIA | 1993 | 478952366.942749 ROMANIA | 1992 | 480227499.738947 RUSSIA | 1998 | 277886925.247709 RUSSIA | 1997 | 479362181.31508 RUSSIA | 1996 | 476744352.996017 RUSSIA | 1995 | 467958681.683361 RUSSIA | 1994 | 481016282.855947 RUSSIA | 1993 | 474287929.579081 RUSSIA | 1992 | 472833661.956428 SAUDI ARABIA | 1998 | 281037805.517404 SAUDI ARABIA | 1997 | 482174267.960705 SAUDI ARABIA | 1996 | 476290851.841433 SAUDI ARABIA | 1995 | 476196537.574893 SAUDI ARABIA | 1994 | 476002342.177561 SAUDI ARABIA | 1993 | 472558275.498825 SAUDI ARABIA | 1992 | 475552539.580199 UNITED KINGDOM | 1998 | 275869794.18462 UNITED KINGDOM | 1997 | 467698526.253843 UNITED KINGDOM | 1996 | 469224158.685112 UNITED KINGDOM | 1995 | 468257286.336842 UNITED KINGDOM | 1994 | 469061810.791944 UNITED KINGDOM | 1993 | 462264130.603062 UNITED KINGDOM | 1992 | 465394060.978163 UNITED STATES | 1998 | 274715924.783055 UNITED STATES | 1997 | 468464270.968741 UNITED STATES | 1996 | 470980423.808751 UNITED STATES | 1995 | 471655814.135008 UNITED STATES | 1994 | 476650707.485535 UNITED STATES | 1993 | 471549258.936103 UNITED STATES | 1992 | 470727913.397229 VIETNAM | 1998 | 274752162.670706 VIETNAM | 1997 | 460758403.905619 VIETNAM | 1996 | 470399578.127183 VIETNAM | 1995 | 462271179.154621 VIETNAM | 1994 | 464493900.975439 VIETNAM | 1993 | 461140111.573529 VIETNAM | 1992 | 473771354.217308 (175 rows) COMMIT; COMMIT