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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE IF NOT EXISTS `test`
( `id` integer unsigned not null auto_increment primary key,
`article` varchar(255) not null,
`prix` decimal(15,2) not null,
`date` date not null
) engine=innoDB
default charset=latin1 collate=latin1_general_ci
row_format=compressed
--------------
--------------
insert into `test` (`article`,`prix`,`date`) values
('T shirt', 7.50, '2016-05-01'),
('chaussettes', 2.10, '2016-05-01'),
('T shirt', 7.00, '2016-05-02'),
('pantalons', 25.00, '2016-05-02'),
('T shirt', 6.50, '2016-05-03'),
('chaussettes', 2.30, '2016-05-03'),
('T shirt', 6.00, '2016-06-04'),
('pantalons', 23.00, '2016-06-04'),
('T shirt', 5.50, '2016-06-05'),
('chaussettes', 2.50, '2016-06-05'),
('pantalons', 21.00, '2016-06-06'),
('chaussettes', 2.70, '2016-06-07'),
('pantalons', 33.00, '2016-06-08')
--------------
--------------
select * from test
--------------
+----+-------------+-------+------------+
| id | article | prix | date |
+----+-------------+-------+------------+
| 1 | T shirt | 7.50 | 2016-05-01 |
| 2 | chaussettes | 2.10 | 2016-05-01 |
| 3 | T shirt | 7.00 | 2016-05-02 |
| 4 | pantalons | 25.00 | 2016-05-02 |
| 5 | T shirt | 6.50 | 2016-05-03 |
| 6 | chaussettes | 2.30 | 2016-05-03 |
| 7 | T shirt | 6.00 | 2016-06-04 |
| 8 | pantalons | 23.00 | 2016-06-04 |
| 9 | T shirt | 5.50 | 2016-06-05 |
| 10 | chaussettes | 2.50 | 2016-06-05 |
| 11 | pantalons | 21.00 | 2016-06-06 |
| 12 | chaussettes | 2.70 | 2016-06-07 |
| 13 | pantalons | 33.00 | 2016-06-08 |
+----+-------------+-------+------------+
--------------
select date_format(date, '%Y-%m') as mois, article, sum(prix) as prix
from test
group by 1,2
order by 1,2
--------------
+---------+-------------+-------+
| mois | article | prix |
+---------+-------------+-------+
| 2016-05 | chaussettes | 4.40 |
| 2016-05 | pantalons | 25.00 |
| 2016-05 | T shirt | 21.00 |
| 2016-06 | chaussettes | 5.20 |
| 2016-06 | pantalons | 77.00 |
| 2016-06 | T shirt | 11.50 |
+---------+-------------+-------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager