CREATE SCHEMA `Prova` ;



CREATE  TABLE `prova`.`Tab` 
(
  `Id` INT NOT NULL ,
  `IdCliente` INT NULL ,
  `Importo` INT NULL ,
  `Data` DATE NULL ,
  `Causale` VARCHAR(45) NULL ,
  PRIMARY KEY (`Id`) 
);



INSERT INTO `prova`.`tab` 
(`Id`, `IdCliente`, `Importo`, `Data`, `Causale`) 
VALUES 
(1, 10, 100, '2012:06:01', 'Carico'),
(2, 10, 50, '2012:06:02', 'Scarico'),
(3, 10, 20, '2012:06:02', 'Scarico'),
(4, 10, 200, '2012:06:02', 'Trasferito'),
(5, 10, 400, '2012:06:03', 'Carico');






USE `prova`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Vista1` AS 
select 
`tab`.`Data` AS `Data`,
sum((case when (`tab`.`Causale` = 'Carico') then `tab`.`Importo` else '0' end)) AS `Caricati`,
sum((case when (`tab`.`Causale` = 'Scarico') then `tab`.`Importo` else '0' end)) AS `Scaricati`,
sum((case when (`tab`.`Causale` = 'Trasferito') then `tab`.`Importo` else '0' end)) AS `Trasferiti`
from 
`tab` 
group by 
`tab`.`Data`
;