SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
mysql> SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;
mysql> SELECT student.student_name,COUNT(*)
FROM student,course
WHERE student.student_id=course.student_id
GROUP BY student_name;
mysql> SELECT COUNT(*) FROM student;
mysql> SELECT COUNT(DISTINCT results) FROM student;
mysql> SELECT student_name,
GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
mysql> SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)
mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)
mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE t(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.33 sec)
mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT c, i FROM t;
+------+------+
| c | i |
+------+------+
| key | 3 |
| key | 4 |
| key | 5 |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5} |
+----------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t;
Query OK, 3 rows affected (0.08 sec)
mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT c, i FROM t;
+------+------+
| c | i |
+------+------+
| key | 3 |
| key | 5 |
| key | 4 |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 4} |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OBJECTAGG(c, i)
OVER () AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 4} |
| {"key": 4} |
| {"key": 4} |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3} |
| {"key": 4} |
| {"key": 5} |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
OVER (ORDER BY i DESC) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5} |
| {"key": 4} |
| {"key": 3} |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
OVER (ORDER BY i
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS json_object
FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5} |
| {"key": 5} |
| {"key": 5} |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
OVER (ORDER BY i) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 3} |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 5} |
+-------------+
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
FROM student
GROUP BY student_name;
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
FROM student
GROUP BY student_name;
CREATE TABLE sales
(
year INT,
country VARCHAR(20),
product VARCHAR(32),
profit INT
);
mysql> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+--------+
mysql> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+--------+
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | Finland | Phone | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
+------+---------+------------+--------+
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+--------+
mysql> SELECT
year, country, product, SUM(profit) AS profit,
GROUPING(year) AS grp_year,
GROUPING(country) AS grp_country,
GROUPING(product) AS grp_product
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+----------+-------------+-------------+
| year | country | product | profit | grp_year | grp_country | grp_product |
+------+---------+------------+--------+----------+-------------+-------------+
| 2000 | Finland | Computer | 1500 | 0 | 0 | 0 |
| 2000 | Finland | Phone | 100 | 0 | 0 | 0 |
| 2000 | Finland | NULL | 1600 | 0 | 0 | 1 |
| 2000 | India | Calculator | 150 | 0 | 0 | 0 |
| 2000 | India | Computer | 1200 | 0 | 0 | 0 |
| 2000 | India | NULL | 1350 | 0 | 0 | 1 |
| 2000 | USA | Calculator | 75 | 0 | 0 | 0 |
| 2000 | USA | Computer | 1500 | 0 | 0 | 0 |
| 2000 | USA | NULL | 1575 | 0 | 0 | 1 |
| 2000 | NULL | NULL | 4525 | 0 | 1 | 1 |
| 2001 | Finland | Phone | 10 | 0 | 0 | 0 |
| 2001 | Finland | NULL | 10 | 0 | 0 | 1 |
| 2001 | USA | Calculator | 50 | 0 | 0 | 0 |
| 2001 | USA | Computer | 2700 | 0 | 0 | 0 |
| 2001 | USA | TV | 250 | 0 | 0 | 0 |
| 2001 | USA | NULL | 3000 | 0 | 0 | 1 |
| 2001 | NULL | NULL | 3010 | 0 | 1 | 1 |
| NULL | NULL | NULL | 7535 | 1 | 1 | 1 |
+------+---------+------------+--------+----------+-------------+-------------+
mysql> SELECT
IF(GROUPING(year), 'All years', year) AS year,
IF(GROUPING(country), 'All countries', country) AS country,
IF(GROUPING(product), 'All products', product) AS product,
SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year | country | product | profit |
+-----------+---------------+--------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | All products | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | All products | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | All products | 1575 |
| 2000 | All countries | All products | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | All products | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | All products | 3000 |
| 2001 | All countries | All products | 3010 |
| All years | All countries | All products | 7535 |
+-----------+---------------+--------------+--------+
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP
HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL | 1600 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+---------+--------+
mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size | quantity |
+------+-------+----------+
| ball | small | 10 |
| ball | large | 20 |
| ball | NULL | 5 |
| hoop | small | 15 |
| hoop | large | 5 |
| hoop | NULL | 3 |
+------+-------+----------+
mysql> SELECT name, size, SUM(quantity) AS quantity
FROM t1
GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size | quantity |
+------+-------+----------+
| ball | NULL | 5 |
| ball | large | 20 |
| ball | small | 10 |
| ball | NULL | 35 |
| hoop | NULL | 3 |
| hoop | large | 5 |
| hoop | small | 15 |
| hoop | NULL | 23 |
| NULL | NULL | 58 |
+------+-------+----------+
mysql> SELECT
IF(GROUPING(name) = 1, 'All items', name) AS name,
IF(GROUPING(size) = 1, 'All sizes', size) AS size,
SUM(quantity) AS quantity
FROM t1
GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name | size | quantity |
+-----------+-----------+----------+
| ball | NULL | 5 |
| ball | large | 20 |
| ball | small | 10 |
| ball | All sizes | 35 |
| hoop | NULL | 3 |
| hoop | large | 5 |
| hoop | small | 15 |
| hoop | All sizes | 23 |
| All items | All sizes | 58 |
+-----------+-----------+----------+
mysql> SELECT * FROM
(SELECT year, SUM(profit) AS profit
FROM sales GROUP BY year WITH ROLLUP) AS dt
ORDER BY year DESC;
+------+--------+
| year | profit |
+------+--------+
| 2001 | 3010 |
| 2000 | 4525 |
| NULL | 7535 |
+------+--------+
mysql> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP
ORDER BY GROUPING(year) DESC;
+------+--------+
| year | profit |
+------+--------+
| NULL | 7535 |
| 2000 | 4525 |
| 2001 | 3010 |
+------+--------+
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP
LIMIT 5;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
+------+---------+------------+--------+
mysql> SELECT year, country, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India | 4525 |
| 2001 | USA | 3010 |
| NULL | USA | 7535 |
+------+---------+--------+
mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India | 4525 |
| 2001 | USA | 3010 |
| NULL | USA | 7535 |
+------+---------+--------+
SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;
mysql> CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL PRIMARY KEY,
-> a VARCHAR(10),
-> b INT
-> );
mysql> INSERT INTO mytable
-> VALUES (1, 'abc', 1000),
-> (2, 'abc', 2000),
-> (3, 'def', 4000);
mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a | SUM(b) |
+------+--------+
| abc | 3000 |
+------+--------+
mysql> DROP TABLE IF EXISTS mytable;
mysql> CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL PRIMARY KEY,
-> a VARCHAR(10),
-> b VARCHAR(10),
-> c INT
-> );
mysql> INSERT INTO mytable
-> VALUES (1, 'abc', 'qrs', 1000),
-> (2, 'abc', 'tuv', 2000),
-> (3, 'def', 'qrs', 4000),
-> (4, 'def', 'tuv', 8000),
-> (5, 'abc', 'qrs', 16000),
-> (6, 'def', 'tuv', 32000);
mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
mysql> SELECT a, b, SUM(c) FROM mytable
-> WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a | b | SUM(c) |
+------+------+--------+
| abc | qrs | 17000 |
+------+------+--------+
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by
SELECT ANY_VALUE(name), MAX(age) FROM t;
SELECT DISTINCT c1, c2 FROM t ORDER BY c3;
SELECT name, COUNT(name) FROM orders
GROUP BY name
HAVING COUNT(name) = 1;
SELECT name, COUNT(name) AS c FROM orders
GROUP BY name
HAVING c = 1;
SELECT id, FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);
SELECT id, FLOOR(value/100) AS val
FROM tbl_name
GROUP BY id, val;
SELECT id, F, id+F
FROM
(SELECT id, FLOOR(value/100) AS F
FROM tbl_name
GROUP BY id, FLOOR(value/100)) AS dt;
SELECT co.Name, COUNT(*)
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY co.Code;
SELECT co.Name, cl.Language,
cl.Percentage * co.Population / 100.0 AS SpokenBy
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;
SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl INNER JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;
SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl LEFT JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;
SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM country co LEFT JOIN countrylanguage cl
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;
CREATE VIEW country2 AS
SELECT co.Code, UPPER(co.Name) AS UpperName,
COUNT(cl.Language) AS OfficialLanguages
FROM country AS co JOIN countrylanguage AS cl
ON cl.CountryCode = co.Code
WHERE cl.isOfficial = 'T'
GROUP BY co.Code;
SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM country2 AS co2 JOIN city ci
ON ci.CountryCode = co2.Code
GROUP BY co2.Code;
SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM
(
SELECT co.Code, UPPER(co.Name) AS UpperName,
COUNT(cl.Language) AS OfficialLanguages
FROM country AS co JOIN countrylanguage AS cl
ON cl.CountryCode=co.Code
WHERE cl.isOfficial='T'
GROUP BY co.Code
) AS co2
JOIN city ci ON ci.CountryCode = co2.Code
GROUP BY co2.Code;