I have to create a report in php:
- customer query ordered in asc.
- Add up the total sales for each customer.
- add total sales per day.
- Add the total of all customers.
All this for a range of dates:
select fecha, razonsocial , precioTotal
from ventas
LEFT JOIN clientes
ON idClientes = clientes_idClientes
where fecha BETWEEN '2016-11-30 00:00:00' AND '2016-11-30 23:59:59'
ORDER BY razonsocial ASC
They tell me that the important data is the totals that they do not have to keep the sales of the day, they only care about the totals.
This is my structure:
CREATE TABLE IF NOT EXISTS `ventas` (
`idVenta` int(255) NOT NULL AUTO_INCREMENT,
`fecha` datetime NOT NULL,
`fecha_pago` datetime DEFAULT NULL,
`nota` int(255) NOT NULL,
`factura` varchar(45) DEFAULT NULL,
`porcDescuento` int(11) NOT NULL,
`tipo` int(1) NOT NULL COMMENT '1 Credito 2 Contado 3 Pendiente de pago 4 Pagado 5 Cancelada ',
`cantidad` int(255) DEFAULT NULL,
`peso` varchar(1000) DEFAULT NULL,
`unidad` int(1) DEFAULT NULL COMMENT '1 pza 2 gr 3 kg',
`precioTotal` decimal(12,2) NOT NULL,
`cancelacion` int(1) DEFAULT NULL,
`productos_idProducto` int(255) NOT NULL,
`usuarios_idUsuario` int(255) NOT NULL,
`clientes_idClientes` int(255) NOT NULL,
`empleados_idRepartidor` int(255) NOT NULL,
`Observaciones` text,
`ObservCancelacion` text,
PRIMARY KEY (`idVenta`),
KEY `fk_ventas_clientes1_idx` (`clientes_idClientes`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5975 ;
CREATE TABLE IF NOT EXISTS `clientes` (
`idClientes` int(255) NOT NULL AUTO_INCREMENT,
`nombre` varchar(45) NOT NULL,
`aPaterno` varchar(45) NOT NULL,
`aMaterno` varchar(45) DEFAULT NULL,
`razonsocial` varchar(50) DEFAULT NULL,
`rfc` varchar(30) NOT NULL,
`email` varchar(100) NOT NULL,
`calle` varchar(300) NOT NULL,
`tipo` int(1) DEFAULT NULL COMMENT '1 credito 2 contado 3 Pendiente de pago',
`tel` int(15) DEFAULT NULL,
`cel` int(20) DEFAULT NULL,
`activo` int(1) NOT NULL DEFAULT '1',
`estados_idEstado` int(255) NOT NULL,
`municipios_idMunicipio` int(255) NOT NULL,
`colonias_idColonia` int(255) NOT NULL,
`sucursales_idSucursal` int(255) NOT NULL,
PRIMARY KEY (`idClientes`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=49 ;
ALTER TABLE `ventas`
ADD CONSTRAINT `fk_ventas_clientes1`
FOREIGN KEY (`clientes_idClientes`)
REFERENCES `clientes` (`idClientes`) ON DELETE NO ACTION ON UPDATE NO ACTION;
[Edition]
Teachers I already have 2 queries out of 3 that my partner @marcelolemus helped me
1- Add the total sales of each customer.
select companyname , SUM(totalprice) from sales LEFT JOIN customers ON customerid = customers_customerid WHERE date BETWEEN '2016-11-30 00:00:00' AND '2016-11-30 23:59:59' group by companyname
3- Add the total of all customers
select SUM(totalprice) from sales WHERE date BETWEEN '2016-11-30 00:00:00' AND '2016-11-30 23:59:59'
2- Add total sales per day select SUM(pricetotal) from sales
select SUM(totalprice) from sales
I tried with this query and it gives me an error that the date field does not exist but I do have the field
select SUM(totalprice) as totalperday from sales WHERE date BETWEEN '2016-11-30 00:00:00' AND '2016-11-30 23:59:59' group by date(datetime)
I modified it but it only gives me 1 record for each customer of the sale between those dates
select SUM(totalprice) as totalperday from sales WHERE date BETWEEN '2016-11-30 00:00:00' AND '2016-11-30 23:59:59' group by date
I'll sleep a little maybe I need it I just got back from the night shift hehe thank you really sometimes between dreams the answer comes out hehe.