Ajuda com colunas calculadas - MySQL
- #SQL Server
- #SQL
- #MySQL
Olá, pessoal.
Gostaria de pedir a ajuda de vocês, para quem souber.
Estou tentando terminar o último projeto do Database Experience Bootcamp e uma das partes é a imagem acima.
Na coluna TotalPrice da tabela Orders, estou tentando fazer uma coluna calculada, onde o valor seria a soma dos serviços prestados (que vem da tabela Services) e as peças usadas (que vem da tabela StockParts).
Utilizei a seguinte fórmula para trazer o resultado:
SELECT round(sum(s.price) + sum(sp.saleprice),2)
FROM services s
JOIN omservices om ON (om.idservices = s.idservices)
JOIN orders o ON (o.idorder = om.idorder)
JOIN omparts omp ON (omp.idorder = o.idorder)
JOIN stockparts sp ON (omp.idparts = sp.idparts)
Ao tentar criar a tabela com o código abaixo:
CREATE TABLE IF NOT EXISTS `mworkshop`.`Orders` (
`idOrder` INT NOT NULL AUTO_INCREMENT,
`Status` VARCHAR(45) NOT NULL,
`ValuationDateStart` DATE NULL,
`ValuationDateEnd` DATE NULL,
`AuthorizationDate` DATE NULL,
`StartDate` DATE NULL,
`EndDate` DATE NULL,
`TotalPrice` FLOAT GENERATED ALWAYS AS (SELECT
round(sum(s.price) + sum(sp.saleprice),2)
FROM services s
JOIN omservices om ON (om.idservices = s.idservices)
JOIN orders o ON (o.idorder = om.idorder)
JOIN omparts omp ON (omp.idorder = o.idorder)
JOIN stockparts sp ON (omp.idparts = sp.idparts)) STORED,
`PaymmentDate` DATE NULL,
`idVehicles` INT NOT NULL,
`idTeams` INT NOT NULL,
`idClients` INT NOT NULL,
PRIMARY KEY (`idOrder`, `idVehicles`, `idTeams`, `idClients`),
INDEX `fk_Order_Vehicles1_idx` (`idVehicles` ASC) VISIBLE,
INDEX `fk_Order_Teams1_idx` (`idTeams` ASC) VISIBLE,
INDEX `fk_Order_Clients1_idx` (`idClients` ASC) VISIBLE,
CONSTRAINT `fk_Order_Vehicles1`
FOREIGN KEY (`idVehicles`)
REFERENCES `mworkshop`.`Vehicles` (`idVehicles`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Order_Teams1`
FOREIGN KEY (`idTeams`)
REFERENCES `mworkshop`.`Teams` (`idTeams`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Order_Clients1`
FOREIGN KEY (`idClients`)
REFERENCES `mworkshop`.`Clients` (`idClients`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Recebo a seguinte mensagem:
Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT sum(s.price)
FROM services s
JOIN omservices om ON (om.idservices = s.id' at line 12
Alguém sabe me dizer se é possível fazer o que quero dessa forma?