Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- mysql> select COUNT(*) as cnt,supplier,MIN(year_ordered) AS first_order,MAX(year_ordered) AS last_order,MIN(year_delivered) AS first_delivery,MAX(year_delivered) AS last_delivery,MAX(year_delivered-year_ordered) AS longest_wait,AVG(year_delivered-year_ordered) AS avg_wait from trades where 1 group by supplier order by cnt desc;
- +-----+------------------------+-------------+------------+----------------+---------------+--------------+-------------------+
- | cnt | supplier | first_order | last_order | first_delivery | last_delivery | longest_wait | avg_wait |
- +-----+------------------------+-------------+------------+----------------+---------------+--------------+-------------------+
- | 193 | USA | 1946 | 2006 | 1950 | 2006-2007 | 10 | 1.35233160621762 |
- | 157 | France | 1949 | 2006 | 1950 | 2007 | 8 | 1.3312101910828 |
- | 144 | UK | 1945 | 2004 | 1950 | 2006 | 8 | 1.41666666666667 |
- | 134 | Germany (FRG) | 1943 | 2007 | 1950-1966 | 2008 | 8 | 1.97014925373134 |
- | 122 | Italy | 1947 | 2006 | 1950 | 2007-2008 | 8 | 1.76229508196721 |
- | 94 | Canada | 1948 | 2006 | 1950 | 2008 | 9 | 1.07446808510638 |
- | 91 | Russia | 1970 | 2007 | 1992 | 2008 | 22 | 2.06593406593407 |
- | 86 | Unknown country | 1949 | 2007 | 1950-1951 | 2008 | 2 | 0.395348837209302 |
- | 83 | Israel | 1954 | 2008 | 1955 | 2008 | 7 | 1.13253012048193 |
- | 82 | USSR | 1947 | 1989 | 1950 | 1990-1991 | 3 | 0.865853658536585 |
- | 77 | Netherlands | 1948 | 2006 | 1950-1952 | 2008 | 9 | 2.12987012987013 |
- | 72 | Sweden | 1943 | 2006 | 1950-1953 | 2008 | 9 | 2.38888888888889 |
- | 67 | Switzerland | 1958 | 2007 | 1959-1960 | 2008 | 4 | 0.925373134328358 |
- | 67 | China | 1952 | 2007 | 1952-1956 | 2008 | 7 | 1.11940298507463 |
- | 64 | Ukraine | 1986 | 2007 | 1992 | 2008 | 11 | 1.015625 |
- | 57 | Spain | 1960 | 2006 | 1960-1969 | 2007-2008 | 6 | 1.45614035087719 |
- | 55 | South Africa | 1959 | 2008 | 1959 | 2008 | 2 | 0.690909090909091 |
- | 37 | Poland | 1958 | 2006 | 1959 | 2007 | 12 | 1.35135135135135 |
- | 35 | Brazil | 1960 | 2003 | 1960 | 2008 | 9 | 1.25714285714286 |
- | 33 | Australia | 1952 | 2006 | 1952 | 2006 | 4 | 1.24242424242424 |
- | 32 | Czechoslovakia | 1946 | 1991 | 1950 | 1991-1992 | 4 | 0.78125 |
- | 31 | Czech Republic | 1991 | 2007 | 1993-1994 | 2008 | 3 | 0.645161290322581 |
- | 30 | Austria | 1962 | 2006 | 1964-1975 | 2007-2008 | 3 | 1 |
- | 26 | Libya | 1971 | 2004 | 1972 | 2006 | 4 | 0.769230769230769 |
- | 26 | Belgium | 1953 | 2006 | 1954-1955 | 2007-2008 | 3 | 1 |
- | 26 | Bulgaria | 1982 | 2008 | 1983 | 2008 | 1 | 0.384615384615385 |
- | 25 | Norway | 1950 | 2002 | 1950 | 2002-2004 | 3 | 0.88 |
- | 24 | Egypt | 1955 | 1996 | 1956 | 1996 | 3 | 0.916666666666667 |
- | 24 | Romania | 1970 | 2004 | 1971 | 2005 | 3 | 0.833333333333333 |
- | 23 | Denmark | 1958 | 2006 | 1960 | 2007-2008 | 5 | 1.60869565217391 |
- | 23 | Finland | 1958 | 2007 | 1960 | 2008 | 4 | 1.34782608695652 |
Add Comment
Please, Sign In to add comment