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
|
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `devis`
--------------
--------------
create table `devis`
( `id` integer unsigned not null auto_increment primary key,
`date` date not null,
`devis` smallint unsigned not null,
`total` decimal(15,2) not null
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `devis` (`date`,`devis`,`total`) VALUES
('2017-12-31', 45, 1.00),
('2018-06-01', 45, 2.00),
('2018-06-02', 75, 3.00),
('2018-06-03', 88, 4.00),
('2018-06-04', 64, 5.00),
('2019-02-18', 64, 6.00)
--------------
--------------
select * from `devis`
--------------
+----+------------+-------+-------+
| id | date | devis | total |
+----+------------+-------+-------+
| 1 | 2017-12-31 | 45 | 1.00 |
| 2 | 2018-06-01 | 45 | 2.00 |
| 3 | 2018-06-02 | 75 | 3.00 |
| 4 | 2018-06-03 | 88 | 4.00 |
| 5 | 2018-06-04 | 64 | 5.00 |
| 6 | 2019-02-18 | 64 | 6.00 |
+----+------------+-------+-------+
--------------
select month(`date`) as mois,
count(*) as nombre,
sum(total) as tarif
from `devis`
where `date` between '2018-01-01' and '2018-12-31'
and `devis` not in (45, 88)
group by mois
--------------
+------+--------+-------+
| mois | nombre | tarif |
+------+--------+-------+
| 6 | 2 | 8.00 |
+------+--------+-------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager