SELECT customer_id, customer_name, COUNT(order_id) as total FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customer_id, customer_name HAVING COUNT(order_id) > 5 ORDER BY COUNT(order_id) DESC;
---
UPDATE customers SET totalorders = ordersummary.total FROM (SELECT customer_id, count(order_id) As total FROM orders GROUP BY customer_id) As ordersummary WHERE customers.customer_id = ordersummary.customer_id
---
SELECT * FROM sometable UNION ALL SELECT * FROM someothertable;
---
SET NAMES 'utf8';
---
CREATE TABLE `PREFIX_address` ( `id_address` int(10) unsigned NOT NULL auto_increment, `id_country` int(10) unsigned NOT NULL, `id_state` int(10) unsigned default NULL, `id_customer` int(10) unsigned NOT NULL default '0', `id_manufacturer` int(10) unsigned NOT NULL default '0', `id_supplier` int(10) unsigned NOT NULL default '0', `id_warehouse` int(10) unsigned NOT NULL default '0', `alias` varchar(32) NOT NULL, `company` varchar(64) default NULL, `lastname` varchar(32) NOT NULL, `firstname` varchar(32) NOT NULL, `address1` varchar(128) NOT NULL, `address2` varchar(128) default NULL, `postcode` varchar(12) default NULL, `city` varchar(64) NOT NULL, `other` text, `phone` varchar(16) default NULL, `phone_mobile` varchar(16) default NULL, `vat_number` varchar(32) default NULL, `dni` varchar(16) DEFAULT NULL, `date_add` datetime NOT NULL, `date_upd` datetime NOT NULL, `active` tinyint(1) unsigned NOT NULL default '1', `deleted` tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (`id_address`), KEY `address_customer` (`id_customer`), KEY `id_country` (`id_country`), KEY `id_state` (`id_state`), KEY `id_manufacturer` (`id_manufacturer`), KEY `id_supplier` (`id_supplier`), KEY `id_warehouse` (`id_warehouse`) ) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
---
CREATE TABLE `PREFIX_alias` ( `id_alias` int(10) unsigned NOT NULL auto_increment, `alias` varchar(255) NOT NULL, `search` varchar(255) NOT NULL, `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`id_alias`), UNIQUE KEY `alias` (`alias`) ) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
---
CREATE TABLE `PREFIX_carrier` ( `id_carrier` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_reference` int(10) unsigned NOT NULL, `id_tax_rules_group` int(10) unsigned DEFAULT '0', `name` varchar(64) NOT NULL, `url` varchar(255) DEFAULT NULL, `active` tinyint(1) unsigned NOT NULL DEFAULT '0', `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', `shipping_handling` tinyint(1) unsigned NOT NULL DEFAULT '1', `range_behavior` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_module` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_free` tinyint(1) unsigned NOT NULL DEFAULT '0', `shipping_external` tinyint(1) unsigned NOT NULL DEFAULT '0', `need_range` tinyint(1) unsigned NOT NULL DEFAULT '0', `external_module_name` varchar(64) DEFAULT NULL, `shipping_method` int(2) NOT NULL DEFAULT '0', `position` int(10) unsigned NOT NULL default '0', `max_width` int(10) DEFAULT 0, `max_height` int(10) DEFAULT 0, `max_depth` int(10) DEFAULT 0, `max_weight` int(10) DEFAULT 0, `grade` int(10) DEFAULT 0, PRIMARY KEY (`id_carrier`), KEY `deleted` (`deleted`,`active`), KEY `id_tax_rules_group` (`id_tax_rules_group`) ) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
---
CREATE TABLE IF NOT EXISTS `PREFIX_specific_price_rule` ( `id_specific_price_rule` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `id_shop` int(11) unsigned NOT NULL DEFAULT '1', `id_currency` int(10) unsigned NOT NULL, `id_country` int(10) unsigned NOT NULL, `id_group` int(10) unsigned NOT NULL, `from_quantity` mediumint(8) unsigned NOT NULL, `price` DECIMAL(20,6), `reduction` decimal(20,6) NOT NULL, `reduction_type` enum('amount','percentage') NOT NULL, `from` datetime NOT NULL, `to` datetime NOT NULL, PRIMARY KEY (`id_specific_price_rule`), KEY `id_product` (`id_shop`,`id_currency`,`id_country`,`id_group`,`from_quantity`,`from`,`to`) ) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
---
UPDATE `PREFIX_configuration` SET value = '6' WHERE name = 'PS_SEARCH_WEIGHT_PNAME'
---
UPDATE `PREFIX_hook_module` SET position = 1 WHERE id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayPayment') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'cheque') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayPaymentReturn') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'cheque') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayHome') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'homeslider') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionAuthentication') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statsdata') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionShopDataDuplication') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'homeslider') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayTop') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blocklanguages') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCustomerAccountAdd') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statsdata') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayCustomerAccount') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'favoriteproducts') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayAdminStatsModules') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statsvisits') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayAdminStatsGraphEngine') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'graphvisifire') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayAdminStatsGridEngine') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'gridhtml') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayLeftColumnProduct') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blocksharefb') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionSearch') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statssearch') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCategoryAdd') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCategoryUpdate') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCategoryDelete') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionAdminMetaSave') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayMyAccountBlock') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'favoriteproducts') OR id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayFooter') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockreinsurance')
---
ALTER TABLE `PREFIX_employee` ADD `bo_color` varchar(32) default NULL AFTER `stats_date_to`
---
INSERT INTO `PREFIX_cms_category_lang` VALUES(1, 3, 'Inicio', '', 'home', NULL, NULL, NULL)
---
INSERT INTO `PREFIX_cms_category` VALUES(1, 0, 0, 1, NOW(), NOW(),0)
---
UPDATE `PREFIX_cms_category` SET `position` = 0
---
ALTER TABLE `PREFIX_customer` ADD `note` text AFTER `secure_key`
---
ALTER TABLE `PREFIX_contact` ADD `customer_service` tinyint(1) NOT NULL DEFAULT 0 AFTER `email`
---
INSERT INTO `PREFIX_specific_price` (`id_product`, `id_shop`, `id_currency`, `id_country`, `id_group`, `priority`, `price`, `from_quantity`, `reduction`, `reduction_type`, `from`, `to`) ( SELECT dq.`id_product`, 1, 1, 0, 1, 0, 0.00, dq.`quantity`, IF(dq.`id_discount_type` = 2, dq.`value`, dq.`value` / 100), IF (dq.`id_discount_type` = 2, 'amount', 'percentage'), '0000-00-00 00:00:00', '0000-00-00 00:00:00' FROM `PREFIX_discount_quantity` dq INNER JOIN `PREFIX_product` p ON (p.`id_product` = dq.`id_product`) )
---
DROP TABLE `PREFIX_discount_quantity`
---
INSERT INTO `PREFIX_specific_price` (`id_product`, `id_shop`, `id_currency`, `id_country`, `id_group`, `priority`, `price`, `from_quantity`, `reduction`, `reduction_type`, `from`, `to`) ( SELECT p.`id_product`, 1, 0, 0, 0, 0, 0.00, 1, IF(p.`reduction_price` > 0, p.`reduction_price`, p.`reduction_percent` / 100), IF(p.`reduction_price` > 0, 'amount', 'percentage'), IF (p.`reduction_from` = p.`reduction_to`, '0000-00-00 00:00:00', p.`reduction_from`), IF (p.`reduction_from` = p.`reduction_to`, '0000-00-00 00:00:00', p.`reduction_to`) FROM `PREFIX_product` p WHERE p.`reduction_price` OR p.`reduction_percent` )
---
ALTER TABLE `PREFIX_product` DROP `reduction_price`, DROP `reduction_percent`, DROP `reduction_from`, DROP `reduction_to`
---
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES ('PS_SPECIFIC_PRICE_PRIORITIES', 'id_shop;id_currency;id_country;id_group', NOW(), NOW()), ('PS_TAX_DISPLAY', 0, NOW(), NOW()), ('PS_SMARTY_FORCE_COMPILE', 1, NOW(), NOW()), ('PS_DISTANCE_UNIT', 'km', NOW(), NOW()), ('PS_STORES_DISPLAY_CMS', 0, NOW(), NOW()), ('PS_STORES_DISPLAY_FOOTER', 0, NOW(), NOW()), ('PS_STORES_SIMPLIFIED', 0, NOW(), NOW()), ('PS_STATSDATA_CUSTOMER_PAGESVIEWS', 1, NOW(), NOW()), ('PS_STATSDATA_PAGESVIEWS', 1, NOW(), NOW()), ('PS_STATSDATA_PLUGINS', 1, NOW(), NOW())
---
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES ('PS_CONDITIONS_CMS_ID', IFNULL((SELECT `id_cms` FROM `PREFIX_cms` WHERE `id_cms` = 3), 0), NOW(), NOW())
---
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` ( `value` text )
---
SET @defaultOOS = (SELECT value FROM `PREFIX_configuration` WHERE name = 'PS_ORDER_OUT_OF_STOCK')
---
UPDATE `PREFIX_product` p SET `cache_default_attribute` = 0 WHERE `id_product` NOT IN (SELECT `id_product` FROM `PREFIX_product_attribute`)
---
INSERT INTO `PREFIX_hook` (`name`, `title`, `description`, `position`) VALUES ('processCarrier', 'Carrier Process', NULL, 0)
---
INSERT INTO `PREFIX_stock_mvt_reason_lang` (`id_stock_mvt_reason`, `id_lang`, `name`) VALUES (1, 1, 'Order'), (1, 2, 'Commande'), (2, 1, 'Missing Stock Movement'), (2, 2, 'Mouvement de stock manquant'), (3, 1, 'Restocking'), (3, 2, 'Réassort')
---
INSERT INTO `PREFIX_meta_lang` (`id_lang`, `id_meta`, `title`, `url_rewrite`) VALUES (1, (SELECT `id_meta` FROM `PREFIX_meta` WHERE `page` = 'authentication'), 'Authentication', 'authentication'), (2, (SELECT `id_meta` FROM `PREFIX_meta` WHERE `page` = 'authentication'), 'Authentification', 'authentification'), (3, (SELECT `id_meta` FROM `PREFIX_meta` WHERE `page` = 'authentication'), 'Autenticación', 'autenticacion')
---
LOCK TABLES `admin_assert` WRITE
---
UNLOCK TABLES
---
DROP TABLE IF EXISTS `admin_role`
---
SELECT * FROM MyTable WHERE 1 = 2;
---
SELECT
---
SELECT Test FROM Test WHERE ( MyColumn = 1 )) AND ((( SomeOtherColumn = 2);
---
ALTER TABLE `test_modify` MODIFY `id` INT(11) UNSIGNED NOT NULL;
---
ALTER TABLE `test_change` CHANGE `id` `_id` BIGINT(20) UNSIGNED NULL;
---
SELECT * LIMIT 1; SELECT a,b,c,d FROM e LIMIT 1, 2; SELECT 1,2,3 WHERE a in (1,2,3,4,5) and b=5;
---
SELECT count - 50 WHERE a-50 = b WHERE 1 and - 50 WHERE -50 = a WHERE a = -50 WHERE 1 - 50 WHERE 1 and -50;
---
SELECT @ and b;
---
SELECT @"weird variable name";
---
SELECT :pdoParam;
---
SELECT "no closing quote
---
SELECT [sqlserver] FROM [escap[e]]d style];
---
SELECT a FROM b LEFT OUTER JOIN c on (d=f);
---
WITH cte AS (SELECT a, b FROM table), RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( SELECT 1, 0, 1 UNION ALL SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10 ) SELECT * FROM fibonacci;
---
WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
---
SELECT a, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp, GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie, GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered, CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg, AVG(starting_salary) OVER w2 AVG, MIN(starting_salary) OVER w2 MIN_STARTING_SALARY, MAX(starting_salary) OVER (w1 ORDER BY hire_date), LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS, LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE, MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond FROM t1 WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date);