Realizare de subtotaluri şi totaluri

Sunt situaţii în care este necesar a se realiza o subtotalizare pentru anumite valori dintr-un tabel, în funcţie de anumite coloane.

În acest articol, am căutat să exemplific prin două variante, ambele având la bază clauza GROUP BY.

Crearea tabelei VANZARI

CREATE TABLE IF NOT EXISTS vanzari (
  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  an int(4) DEFAULT NULL,
  tara varchar(50) CHARACTER SET utf8 COLLATE utf8_romanian_ci DEFAULT NULL,
  produs varchar(50) CHARACTER SET utf8 COLLATE utf8_romanian_ci DEFAULT NULL,
  profit int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS vanzari (

id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

an int(4) DEFAULT NULL,

tara varchar(50) CHARACTER SET utf8 COLLATE utf8_romanian_ci DEFAULT NULL,

produs varchar(50) CHARACTER SET utf8 COLLATE utf8_romanian_ci DEFAULT NULL,

profit int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Popularea tabelei

INSERT INTO vanzari (an, tara, produs, profit) VALUES
(2018, 'Finland', 'Phone', 40),
(2018, 'Finland', 'Computer', 1200),
(2018, 'Finland', 'Phone', 60),
(2018, 'India', 'Calculator', 150),
(2018, 'India', 'Computer', 700),
(2018, 'India', 'Computer', 500),
(2018, 'USA', 'Calculator', 75),
(2018, 'USA', 'Computer', 450),
(2018, 'USA', 'Computer', 500),
(2018, 'USA', 'Computer', 550),
(2018, 'Finland', 'Computer', 300),
(2020, 'Finland', 'Phone', 7),
(2020, 'Finland', 'Phone', 3),
(2020, 'USA', 'Calculator', 50),
(2020, 'USA', 'Computer', 2000),
(2020, 'USA', 'TV', 250),
(2020, 'USA', 'Computer', 700);

INSERT INTO vanzari (an, tara, produs, profit) VALUES

(2018, 'Finland', 'Phone', 40),

(2018, 'Finland', 'Computer', 1200),

(2018, 'Finland', 'Phone', 60),

(2018, 'India', 'Calculator', 150),

(2018, 'India', 'Computer', 700),

(2018, 'India', 'Computer', 500),

(2018, 'USA', 'Calculator', 75),

(2018, 'USA', 'Computer', 450),

(2018, 'USA', 'Computer', 500),

(2018, 'USA', 'Computer', 550),

(2018, 'Finland', 'Computer', 300),

(2020, 'Finland', 'Phone', 7),

(2020, 'Finland', 'Phone', 3),

(2020, 'USA', 'Calculator', 50),

(2020, 'USA', 'Computer', 2000),

(2020, 'USA', 'TV', 250),

(2020, 'USA', 'Computer', 700);

Rezultat

id	an	tara	produs	profit
39	2018	Finland	Phone	40
40	2018	Finland	Computer	1200
41	2018	Finland	Phone	60
42	2018	India	Calculator	150
43	2018	India	Computer	700
44	2018	India	Computer	500
45	2018	USA	Calculator	75
46	2018	USA	Computer	450
47	2018	USA	Computer	500
48	2018	USA	Computer	550
49	2018	Finland	Computer	300
50	2020	Finland	Phone	7
51	2020	Finland	Phone	3
52	2020	USA	Calculator	50
53	2020	USA	Computer	2000
54	2020	USA	TV	250
55	2020	USA	Computer	700

Gruparea şi însumarea valorilor

SELECT an, tara, produs, SUM(profit) AS profit
FROM vanzari 
GROUP BY an, tara, produs
ORDER BY an, tara

SELECT an, tara, produs, SUM(profit) AS profit

FROM vanzari

GROUP BY an, tara, produs

ORDER BY an, tara

Rezultat

an	tara	produs	profit
2018	Finland	Computer	1500
2018	Finland	Phone	100
2018	India	Calculator	150
2018	India	Computer	1200
2018	USA	Calculator	75
2018	USA	Computer	1500
2020	Finland	Phone	10
2020	USA	Calculator	50
2020	USA	Computer	2700
2020	USA	TV	250

Subtotaluri şi totaluri cu ROLLUP

SELECT an, tara, produs, SUM(profit) AS profit
FROM vanzari 
GROUP BY an, tara, produs WITH ROLLUP

SELECT an, tara, produs, SUM(profit) AS profit

FROM vanzari

GROUP BY an, tara, produs WITH ROLLUP

Rezultat

an	tara	produs	profit
2018	Finland	Computer	1500
2018	Finland	Phone	100
2018	Finland	NULL	1600
2018	India	Calculator	150
2018	India	Computer	1200
2018	India	NULL	1350
2018	USA	Calculator	75
2018	USA	Computer	1500
2018	USA	NULL	1575
2018	NULL	NULL	4525
2020	Finland	Phone	10
2020	Finland	NULL	10
2020	USA	Calculator	50
2020	USA	Computer	2700
2020	USA	TV	250
2020	USA	NULL	3000
2020	NULL	NULL	3010
NULL	NULL	NULL	7535

Variantă de subtotal cu UNION

SELECT an, tara, produs, profit FROM (
    SELECT 1 AS rang, an, tara, produs, SUM(profit) AS profit
    FROM vanzari 
    GROUP BY an, tara, produs
    UNION
    SELECT 2 AS rang, an, tara, 'Subtotal TARA' AS produs, SUM(profit) AS profit
    FROM vanzari 
    GROUP BY an, tara
) t 
ORDER BY an, tara, rang

SELECT an, tara, produs, profit FROM (

SELECT 1 AS rang, an, tara, produs, SUM(profit) AS profit

FROM vanzari

GROUP BY an, tara, produs

UNION

SELECT 2 AS rang, an, tara, 'Subtotal TARA' AS produs, SUM(profit) AS profit

FROM vanzari

GROUP BY an, tara

) t

ORDER BY an, tara, rang

Rezultat

an	tara	produs	profit
2018	Finland	Computer	1500
2018	Finland	Phone	100
2018	Finland	Subtotal TARA	1600
2018	India	Computer	1200
2018	India	Calculator	150
2018	India	Subtotal TARA	1350
2018	USA	Calculator	75
2018	USA	Computer	1500
2018	USA	Subtotal TARA	1575
2020	Finland	Phone	10
2020	Finland	Subtotal TARA	10
2020	USA	Calculator	50
2020	USA	Computer	2700
2020	USA	TV	250
2020	USA	Subtotal TARA	3000

Sursa: MySQL.com

L	Ma	Mi	J	V	S	D
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Lasă un răspuns Anulează răspunsul

Utilităţi

Categorii

Pagini

Articole recente

Arhive

Proiecte

Crearea tabelei VANZARI

Popularea tabelei

Rezultat

Gruparea şi însumarea valorilor

Rezultat

Subtotaluri şi totaluri cu ROLLUP

Rezultat

Variantă de subtotal cu UNION

Rezultat

Author: Ovidiu.S

Lasă un răspuns Anulează răspunsul

Utilităţi

Categorii

Pagini

Articole recente

Arhive

Proiecte

Etichete