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
| bon sur msql j ai fais une table "transaction"
/***************************************
--
-- Structure de la table `transaction`
--
CREATE TABLE IF NOT EXISTS `transaction` (
`id` int(11) NOT NULL,
`type` int(11) NOT NULL,
`label1` text,
`label2` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Contenu de la table `transaction`
--
INSERT INTO `transaction` (`id`, `type`, `label1`, `label2`) VALUES
(1, 1, 'bob', 'jean'),
(2, 2, 'bob', 'fred'),
(3, 1, 'bob', 'trouver'),
(4, 1, 'bob', NULL),
(5, 2, 'arthur', NULL),
(6, 2, 'bob', NULL),
(7, 1, 'bob', NULL),
(8, 1, 'bob', NULL);
/*******************************************
j'execute ca :
SELECT DISTINCT label1, SUM( OK ) AS nbType1, SUM( EC ) AS nbType2
FROM (
SELECT DISTINCT label1, (
CASE WHEN TYPE =1
THEN COUNT(
TYPE )
ELSE 0
END
) AS OK, (
CASE WHEN TYPE =2
THEN COUNT(
TYPE )
ELSE 0
END
) AS EC
FROM transaction
WHERE LABEL1 = 'bob'
GROUP BY TYPE
) AS fred
et j 'obtient
-------------------
| label1 | OK | EC |
-------------------
| | | |
| bob | 5| 2|
| | | | |
Partager