| Album » OptE » q8 (11 Slides) | [Page 1 of 1] :: Jump To |
SELECT
O_YEAR,
SUM(CASE
WHEN NATION = 'BRAZIL' THEN VOLUME
ELSE 0
END) / SUM(VOLUME)
FROM
(
SELECT
YEAR(O_ORDERDATE) AS O_YEAR,
L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
N2.N_NAME AS NATION
FROM
PART,
SUPPLIER,
LINEITEM,
ORDERS,
CUSTOMER,
NATION N1,
NATION N2,
REGION
WHERE
P_PARTKEY = L_PARTKEY
AND S_SUPPKEY = L_SUPPKEY
AND L_ORDERKEY = O_ORDERKEY
AND O_CUSTKEY = C_CUSTKEY
AND C_NATIONKEY = N1.N_NATIONKEY
AND N1.N_REGIONKEY = R_REGIONKEY
AND R_NAME = 'AMERICA'
AND S_NATIONKEY = N2.N_NATIONKEY
AND O_ORDERDATE BETWEEN '1995-01-01' AND '1996-12-31'
AND P_TYPE = 'ECONOMY ANODIZED STEEL'
AND S_ACCTBAL :varies
AND L_EXTENDEDPRICE :varies
) AS ALL_NATIONS
GROUP BY
O_YEAR
ORDER BY
O_YEAR
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
