MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.25


CREATE TABLE item
(
  i_name VARCHAR (20) NOT NULL,
  i_qty  TINYINT NOT NULL,
  i_price SMALLINT NOT NULL,
  
  KEY(i_name)
);
INSERT INTO item
VALUES
('Tea'   , 1, 10),
('Tea'   , 1, 10),
('Tea'   , 1, 10),
('Coffee', 1, 10),
('Coffee', 1, 10),
('Idly'  , 1, 23),
('Parota', 1, 25),
('Parota', 1, 25),
('Dosa'  , 1, 35),
('Meals' , 1, 75);
SELECT * FROM item;


i_name
i_qty
i_price


Tea
1
10

Tea
1
10

Tea
1
10

Coffee
1
10

Coffee
1
10

Idly
1
23

Parota
1
25

Parota
1
25

Dosa
1
35

Meals
1
75


SELECT
  i_name,
  i_price,
  COUNT(i_qty) AS "Number sold",
  SUM(i_price) AS "Total revenue"
FROM item
GROUP BY i_name, i_price
ORDER BY i_price;


i_name
i_price
Number sold
Total revenue


Tea
10
3
30

Coffee
10
2
20

Idly
23
1
23

Parota
25
2
50

Dosa
35
1
35

Meals
75
1
75


SELECT DISTINCT
  i_name,
  i_price,
  COUNT(i_qty) OVER (PARTITION BY i_name ORDER BY i_name) AS "Number sold",
  SUM(i_price) OVER (PARTITION BY i_name ORDER BY i_name) AS "Total revenue"
FROM item
ORDER BY i_price;


i_name
i_price
Number sold
Total revenue


Coffee
10
2
20

Tea
10
3
30

Idly
23
1
23

Parota
25
2
50

Dosa
35
1
35

Meals
75
1
75


EXPLAIN ANALYZE
SELECT
  i_name,
  i_price,
  COUNT(i_qty) AS "Number sold",
  SUM(i_price) AS "Total revenue"
FROM item
GROUP BY i_name, i_price
ORDER BY i_price;


EXPLAIN


-> Sort: item.i_price  (actual time=0.116..0.117 rows=6 loops=1)
    -> Table scan on <temporary>  (actual time=0.001..0.002 rows=6 loops=1)
        -> Aggregate using temporary table  (actual time=0.104..0.106 rows=6 loops=1)
            -> Table scan on item  (cost=1.25 rows=10) (actual time=0.013..0.045 rows=10 loops=1)



EXPLAIN ANALYZE
SELECT DISTINCT
  i_name,
  i_price,
  COUNT(i_qty) OVER (PARTITION BY i_name ORDER BY i_name) AS "Number sold",
  SUM(i_price) OVER (PARTITION BY i_name ORDER BY i_name) AS "Total revenue"
FROM item
ORDER BY i_price;


EXPLAIN


-> Sort: item.i_price  (actual time=0.151..0.152 rows=6 loops=1)
    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.001 rows=6 loops=1)
        -> Temporary table with deduplication  (cost=2.50..2.50 rows=0) (actual time=0.140..0.141 rows=6 loops=1)
            -> Window aggregate with buffering: sum(item.i_price) OVER (PARTITION BY item.i_name ORDER BY item.i_name )   (actual time=0.093..0.124 rows=10 loops=1)
                -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.002 rows=10 loops=1)
                    -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.081..0.085 rows=10 loops=1)
                        -> Window aggregate with buffering: count(item.i_qty) OVER (PARTITION BY item.i_name ORDER BY item.i_name )   (actual time=0.053..0.076 rows=10 loops=1)
                            -> Sort: item.i_name, item.i_name  (cost=1.25 rows=10) (actual time=0.038..0.041 rows=10 loops=1)
                                -> Table scan on item  (cost=1.25 rows=10) (actual time=0.012..0.023 rows=10 loops=1)



SET PROFILING = 1;
SELECT
  i_name,
  i_price,
  COUNT(i_qty) AS "Number sold",
  SUM(i_price) AS "Total revenue"
FROM item
GROUP BY i_name, i_price
ORDER BY i_price;


i_name
i_price
Number sold
Total revenue


Tea
10
3
30

Coffee
10
2
20

Idly
23
1
23

Parota
25
2
50

Dosa
35
1
35

Meals
75
1
75


SELECT DISTINCT
  i_name,
  i_price,
  COUNT(i_qty) OVER (PARTITION BY i_name ORDER BY i_name) AS "Number sold",
  SUM(i_price) OVER (PARTITION BY i_name ORDER BY i_name) AS "Total revenue"
FROM item
ORDER BY i_price;


i_name
i_price
Number sold
Total revenue


Coffee
10
2
20

Tea
10
3
30

Idly
23
1
23

Parota
25
2
50

Dosa
35
1
35

Meals
75
1
75


SET PROFILING = 0;
SHOW PROFILES;


Query_ID
Duration
Query


1
0.00047100
SELECT
  i_name,
  i_price,
  COUNT(i_qty) AS "Number sold",
  SUM(i_price) AS "Total revenue"
FROM item
GROUP BY i_name, i_price
ORDER BY i_price

2
0.00054450
SELECT DISTINCT
  i_name,
  i_price,
  COUNT(i_qty) OVER (PARTITION BY i_name ORDER BY i_name) AS "Number sold",
  SUM(i_price) OVER (PARTITION BY i_name ORDER BY i_name) AS "Total revenue"
FROM item
ORDER BY i_price

i_name	i_qty	i_price
Tea	1	10
Tea	1	10
Tea	1	10
Coffee	1	10
Coffee	1	10
Idly	1	23
Parota	1	25
Parota	1	25
Dosa	1	35
Meals	1	75

Query_ID	Duration	Query
1	0.00047100	SELECT i_name, i_price, COUNT(i_qty) AS "Number sold", SUM(i_price) AS "Total revenue" FROM item GROUP BY i_name, i_price ORDER BY i_price
2	0.00054450	SELECT DISTINCT i_name, i_price, COUNT(i_qty) OVER (PARTITION BY i_name ORDER BY i_name) AS "Number sold", SUM(i_price) OVER (PARTITION BY i_name ORDER BY i_name) AS "Total revenue" FROM item ORDER BY i_price