9 lines
538 B
MySQL
9 lines
538 B
MySQL
|
-- TPC_H Query 22 - Global Sales Opportunity */
|
||
|
|
SELECT CNTRYCODE, COUNT(*) AS NUMCUST, SUM(C_ACCTBAL) AS TOTACCTBAL
|
||
|
|
FROM (SELECT SUBSTRING(C_PHONE,1,2) AS CNTRYCODE, C_ACCTBAL
|
||
|
|
FROM CUSTOMER WHERE SUBSTRING(C_PHONE,1,2) IN ('13', '31', '23', '29', '30', '18', '17') AND
|
||
|
|
C_ACCTBAL > (SELECT AVG(C_ACCTBAL) FROM CUSTOMER WHERE C_ACCTBAL > 0.00 AND
|
||
|
|
SUBSTRING(C_PHONE,1,2) IN ('13', '31', '23', '29', '30', '18', '17')) AND
|
||
|
|
NOT EXISTS ( SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY)) AS CUSTSALE
|
||
|
|
GROUP BY CNTRYCODE
|
||
|
|
ORDER BY CNTRYCODE;
|