Please try the normal tpch q22.
mysql> select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal from ( select substr(c_phone, 1, 2) as cntrycode, c_acctbal from customer where substr(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 substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')) and not exists ( select * from orders where o_custkey = c_custkey ) ) custsale group by cntrycode order by cntrycode; +-----------+---------+------------+ | cntrycode | numcust | totacctbal | +-----------+---------+------------+ | 13 | 888 | 6737713.99 | | 17 | 861 | 6460573.72 | | 18 | 964 | 7236687.40 | | 23 | 892 | 6701457.95 | | 29 | 948 | 7158866.63 | | 30 | 909 | 6808436.13 | | 31 | 922 | 6806670.18 | +-----------+---------+------------+ 7 rows in set (1.52 sec)
Please try the normal tpch q22.
mysql> select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal from ( select substr(c_phone, 1, 2) as cntrycode, c_acctbal from customer where substr(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 substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')) and not exists ( select * from orders where o_custkey = c_custkey ) ) custsale group by cntrycode order by cntrycode; -----+- ------- -+----- ------- + -----+- ------- -+----- ------- + -----+- ------- -+----- ------- +
+------
| cntrycode | numcust | totacctbal |
+------
| 13 | 888 | 6737713.99 |
| 17 | 861 | 6460573.72 |
| 18 | 964 | 7236687.40 |
| 23 | 892 | 6701457.95 |
| 29 | 948 | 7158866.63 |
| 30 | 909 | 6808436.13 |
| 31 | 922 | 6806670.18 |
+------
7 rows in set (1.52 sec)