MySQL 8.0

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

select version();


version()


8.0.30


CREATE TABLE item
(
  i_name VARCHAR (20) NOT NULL,
  i_qty  TINYINT NOT NULL,
  i_price SMALLINT NOT NULL
);
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;

Records: 10  Duplicates: 0  Warnings: 0




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

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