22 lines
541 B
MySQL
22 lines
541 B
MySQL
|
DROP TABLE IF EXISTS ProductZeroPurchasesByProvince;
|
||
|
|
|
||
|
|
CREATE TABLE ProductZeroPurchasesByProvince AS
|
||
|
|
SELECT
|
||
|
|
c.name AS CountryName,
|
||
|
|
pro.name AS ProvinceName,
|
||
|
|
COUNT(pro.name) AS cnt
|
||
|
|
FROM
|
||
|
|
Purchases pur
|
||
|
|
JOIN
|
||
|
|
Province pro ON pur.product = 0 AND pur.province = pro.rowid
|
||
|
|
JOIN
|
||
|
|
Country c ON pro.country = c.code
|
||
|
|
GROUP BY
|
||
|
|
c.code, pro.name;
|
||
|
|
|
||
|
|
DROP INDEX IF EXISTS Province_index;
|
||
|
|
CREATE INDEX Province_index ON Province(name,country)
|
||
|
|
|
||
|
|
DROP INDEX IF EXISTS Country_index;
|
||
|
|
CREATE INDEX Country_index ON Country(code)
|