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 '%floral%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267775329.676026 ALGERIA | 1997 | 451626104.852539 ALGERIA | 1996 | 459633230.242995 ALGERIA | 1995 | 461652812.468436 ALGERIA | 1994 | 459687145.461171 ALGERIA | 1993 | 459872578.69691 ALGERIA | 1992 | 459626235.594848 ARGENTINA | 1998 | 279146095.059097 ARGENTINA | 1997 | 473882946.7721 ARGENTINA | 1996 | 477032564.47867 ARGENTINA | 1995 | 470096065.914182 ARGENTINA | 1994 | 477782822.085652 ARGENTINA | 1993 | 467819020.765048 ARGENTINA | 1992 | 473627763.61241 BRAZIL | 1998 | 272849544.774041 BRAZIL | 1997 | 463055730.470433 BRAZIL | 1996 | 461382423.441703 BRAZIL | 1995 | 470404160.578998 BRAZIL | 1994 | 467742538.710632 BRAZIL | 1993 | 468964826.092004 BRAZIL | 1992 | 467954468.964887 CANADA | 1998 | 277817173.566255 CANADA | 1997 | 479153810.111741 CANADA | 1996 | 476831505.182772 CANADA | 1995 | 474620851.13732 CANADA | 1994 | 471787070.782772 CANADA | 1993 | 471653046.192838 CANADA | 1992 | 472720420.007204 CHINA | 1998 | 266292717.615616 CHINA | 1997 | 469407611.376521 CHINA | 1996 | 469141576.773804 CHINA | 1995 | 462730380.196087 CHINA | 1994 | 472631366.860034 CHINA | 1993 | 474484787.94351 CHINA | 1992 | 468471610.100047 EGYPT | 1998 | 276766725.352239 EGYPT | 1997 | 463294322.174773 EGYPT | 1996 | 468559076.334029 EGYPT | 1995 | 467561185.938861 EGYPT | 1994 | 463844499.558014 EGYPT | 1993 | 459964796.65559 EGYPT | 1992 | 462538228.720793 ETHIOPIA | 1998 | 269925886.817421 ETHIOPIA | 1997 | 450176073.46575 ETHIOPIA | 1996 | 447246974.39186 ETHIOPIA | 1995 | 460779470.656683 ETHIOPIA | 1994 | 447850223.358421 ETHIOPIA | 1993 | 449459588.297033 ETHIOPIA | 1992 | 455820938.401899 FRANCE | 1998 | 269493883.711328 FRANCE | 1997 | 457621009.389726 FRANCE | 1996 | 463699827.752476 FRANCE | 1995 | 461187135.489364 FRANCE | 1994 | 458075290.831261 FRANCE | 1993 | 473236462.737226 FRANCE | 1992 | 460864871.114946 GERMANY | 1998 | 277015828.963479 GERMANY | 1997 | 476163574.330782 GERMANY | 1996 | 471051731.212879 GERMANY | 1995 | 474345619.066478 GERMANY | 1994 | 471744120.859118 GERMANY | 1993 | 476408449.229011 GERMANY | 1992 | 479022355.726764 INDIA | 1998 | 275585411.655558 INDIA | 1997 | 464078458.527976 INDIA | 1996 | 470451331.138829 INDIA | 1995 | 467610934.559814 INDIA | 1994 | 462663479.299551 INDIA | 1993 | 467396223.04915 INDIA | 1992 | 473459719.328809 INDONESIA | 1998 | 273598872.341324 INDONESIA | 1997 | 465847160.497721 INDONESIA | 1996 | 470826296.551396 INDONESIA | 1995 | 467498757.581008 INDONESIA | 1994 | 462102840.036111 INDONESIA | 1993 | 460016074.111843 INDONESIA | 1992 | 466940746.408597 IRAN | 1998 | 268635463.163343 IRAN | 1997 | 457021343.991215 IRAN | 1996 | 453935619.487065 IRAN | 1995 | 450065557.596219 IRAN | 1994 | 452656530.270292 IRAN | 1993 | 459834707.272292 IRAN | 1992 | 461075343.253701 IRAQ | 1998 | 277376223.140641 IRAQ | 1997 | 464453511.548836 IRAQ | 1996 | 472322626.653384 IRAQ | 1995 | 474901288.732991 IRAQ | 1994 | 474098218.374181 IRAQ | 1993 | 478069997.807574 IRAQ | 1992 | 469183031.372277 JAPAN | 1998 | 267669442.286487 JAPAN | 1997 | 464519236.449643 JAPAN | 1996 | 458368935.003319 JAPAN | 1995 | 459511339.846594 JAPAN | 1994 | 451507252.027702 JAPAN | 1993 | 455798708.630283 JAPAN | 1992 | 462081040.410189 JORDAN | 1998 | 270324276.543661 JORDAN | 1997 | 468885582.940003 JORDAN | 1996 | 464517264.927594 JORDAN | 1995 | 458215252.410787 JORDAN | 1994 | 464648406.166712 JORDAN | 1993 | 461024593.669638 JORDAN | 1992 | 465717423.520574 KENYA | 1998 | 273615654.439983 KENYA | 1997 | 455663337.734216 KENYA | 1996 | 455611900.78396 KENYA | 1995 | 453122895.385513 KENYA | 1994 | 460398014.872119 KENYA | 1993 | 464058677.314088 KENYA | 1992 | 456738091.201906 MOROCCO | 1998 | 272830477.084968 MOROCCO | 1997 | 452725003.256509 MOROCCO | 1996 | 464739627.387012 MOROCCO | 1995 | 470578785.697041 MOROCCO | 1994 | 472759782.399852 MOROCCO | 1993 | 459133064.983334 MOROCCO | 1992 | 462547645.062689 MOZAMBIQUE | 1998 | 262532348.714704 MOZAMBIQUE | 1997 | 449222254.296242 MOZAMBIQUE | 1996 | 451697563.214479 MOZAMBIQUE | 1995 | 450771604.198953 MOZAMBIQUE | 1994 | 453186243.474773 MOZAMBIQUE | 1993 | 449382125.295672 MOZAMBIQUE | 1992 | 448997176.524752 PERU | 1998 | 272693311.560429 PERU | 1997 | 459061772.322296 PERU | 1996 | 464316193.888948 PERU | 1995 | 456838126.951512 PERU | 1994 | 455614156.112777 PERU | 1993 | 453537517.486377 PERU | 1992 | 454942419.073906 ROMANIA | 1998 | 267471420.939794 ROMANIA | 1997 | 464048611.27098 ROMANIA | 1996 | 467676464.835889 ROMANIA | 1995 | 462144220.444483 ROMANIA | 1994 | 468437544.687663 ROMANIA | 1993 | 456665447.246319 ROMANIA | 1992 | 465874771.991152 RUSSIA | 1998 | 275703761.736204 RUSSIA | 1997 | 461927824.191275 RUSSIA | 1996 | 470126164.465013 RUSSIA | 1995 | 469031211.487852 RUSSIA | 1994 | 467944124.907111 RUSSIA | 1993 | 468526770.479284 RUSSIA | 1992 | 467189398.794933 SAUDI ARABIA | 1998 | 275643661.813681 SAUDI ARABIA | 1997 | 462428079.576941 SAUDI ARABIA | 1996 | 455650215.544054 SAUDI ARABIA | 1995 | 461197113.380027 SAUDI ARABIA | 1994 | 460215162.205997 SAUDI ARABIA | 1993 | 456879365.613191 SAUDI ARABIA | 1992 | 461339182.431248 UNITED KINGDOM | 1998 | 276828006.550019 UNITED KINGDOM | 1997 | 457517786.973719 UNITED KINGDOM | 1996 | 464545217.86862 UNITED KINGDOM | 1995 | 466860966.889613 UNITED KINGDOM | 1994 | 465265563.405335 UNITED KINGDOM | 1993 | 465666692.748955 UNITED KINGDOM | 1992 | 471366308.454209 UNITED STATES | 1998 | 277474718.933324 UNITED STATES | 1997 | 477543146.957353 UNITED STATES | 1996 | 472954985.392035 UNITED STATES | 1995 | 474303515.555869 UNITED STATES | 1994 | 478369031.236206 UNITED STATES | 1993 | 477631284.820175 UNITED STATES | 1992 | 471116177.320673 VIETNAM | 1998 | 267327369.283457 VIETNAM | 1997 | 459943549.182707 VIETNAM | 1996 | 464084485.271089 VIETNAM | 1995 | 449520140.884303 VIETNAM | 1994 | 452806227.407435 VIETNAM | 1993 | 465647154.54629 VIETNAM | 1992 | 454879892.027399 (175 rows) COMMIT; COMMIT