Home / Questions / 387937
Accepted
Caruso
Asked: 2020-09-08 03:44:37 +0800 CST 2020-09-08 03:44:37 +0800 CST

I can't group receipts with group by

  • 772

I have a ticket table in MySQL, the client can pay a little by card and another part in cash. For that I have a movement table with the movement id that leads to another table (card, cash, current account, etc). But when I want to consult with group by, it returns the tickets once but only shows me the cash delivered:

   $query="SELECT * from ticket left join tipocomprobante on 
   tipocomprobante.idtipocomp=ticket.idtipocomp left join cliente on 
   ticket.idc=cliente.idcliente left join movimiento on movimiento.idticket=ticket.idticket 
    WHERE cajero=".$idcajero." AND sesion=".$idsesion." group by ticket.idticket;";

I have tried the query in phpMyAdmin and it does not return the card movements:

  CREATE TABLE `movimiento` (
  `idmov` int(11) NOT NULL,
  `tipomov` int(11) DEFAULT NULL,
  `importe` decimal(10,2) DEFAULT NULL,
  `idticket` int(11) NOT NULL,
  `idcliente` int(11) DEFAULT NULL,
  `idsesion` int(11) DEFAULT NULL,
  `fecha` varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL
)

CREATE TABLE `ticket` (
  `idticket` int(11) NOT NULL,
  `fecha` varchar(11) COLLATE utf8_spanish_ci DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `vuelto` decimal(10,2) DEFAULT NULL,
  `hora` varchar(10) COLLATE utf8_spanish_ci DEFAULT NULL,
  `idtipocomp` int(11) DEFAULT NULL,
  `idc` int(11) NOT NULL,
  `cae` varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL,
  `fechavto` date DEFAULT NULL,
  `cajero` int(11) DEFAULT NULL,
  `sesion` int(11) DEFAULT NULL,
  `pago` varchar(11) COLLATE utf8_spanish_ci DEFAULT NULL
) 

CREATE TABLE `tipomov` (
  `idtipomov` int(11) NOT NULL,
  `nombre` varchar(100) COLLATE utf8_spanish_ci DEFAULT NULL
)

I edit the question: enter image description here

I have that table where I show the movements of the session. There the idea is that all the tickets appear, with the amount paid in cash, and the amount with a debit or credit card. As you can see, it generates 2 rows in the table for the same ticket, I would like to have each ticket in a single row. Otherwise, you would have to modify the structure of the ticket table and directly add the amount of cash and card. And then check if they are different from 0.

mysql

1 Answers

  1. Best Answer

    He group bywill hide info from you because you group by the ticket without using aggregation functions in the other data; the engine doesn't know what to show you, so it shows you the first row of each ticket in the set.

    Let's say these are the sample tables and data:

    CREATE TABLE `movimiento` (
  `idmov` int(11) NOT NULL,
  `tipomov` int(11) DEFAULT NULL,
  `importe` decimal(10,2) DEFAULT NULL,
  `idticket` int(11) NOT NULL,
  `idcliente` int(11) DEFAULT NULL,
  `idsesion` int(11) DEFAULT NULL,
  `fecha` varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL
);
insert into movimiento
values
(1, 1, 123.45, 1, 1, 1, now() ),
(2, 2, 22.44, 1, 1, 1, now());

CREATE TABLE `ticket` (
  `idticket` int(11) NOT NULL,
  `fecha` varchar(11) COLLATE utf8_spanish_ci DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `vuelto` decimal(10,2) DEFAULT NULL,
  `hora` varchar(10) COLLATE utf8_spanish_ci DEFAULT NULL,
  `idtipocomp` int(11) DEFAULT NULL,
  `idc` int(11) NOT NULL,
  `cae` varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL,
  `fechavto` date DEFAULT NULL,
  `cajero` int(11) DEFAULT NULL,
  `sesion` int(11) DEFAULT NULL,
  `pago` varchar(11) COLLATE utf8_spanish_ci DEFAULT NULL
); 
insert into ticket
values
(1, 'fecha', 145.89, 0.0, 'hora', 1, 123, 'cae', now(), 333, 1, 'pago');

CREATE TABLE `tipomov` (
  `idtipomov` int(11) NOT NULL,
  `nombre` varchar(100) COLLATE utf8_spanish_ci DEFAULT NULL
); 
insert into tipomov
values
(1, 'efectivo'),
(2, 'cheque');

    I have a ticket, two movements, each one of a type. I removed the joins with customers and vouchers because they seem to be not relevant and I don't have any data either.

    If I use a query without grouping:

    SELECT * 
from movimiento 
left join ticket on movimiento.idticket=ticket.idticket 
WHERE 
cajero=333 AND sesion=1

    I get two rows, each with the movement info and the associated ticket data:

    idmov   tipomov     importe     idticket    idcliente   idsesion    fecha   idticket    fecha   total   vuelto  hora    idtipocomp  idc     cae     fechavto    cajero  sesion  pago
1   1   123.45  1   1   1   2020-09-07 12:10:18     1   fecha   145.89  0   hora    1   123     cae     2020-09-07  333     1   pago
2   2   22.44   1   1   1   2020-09-07 12:10:18     1   fecha   145.89  0   hora    1   123     cae     2020-09-07  333     1   pago

    I would understand that this is what you are looking for. Now, tell us that you want to do the group by in the ticket. could you make a

    SELECT ticket.*, sum(movimiento.importe)
from movimiento 
left join ticket on movimiento.idticket=ticket.idticket 
WHERE 
cajero=333 AND sesion=1

    And you would get a single row with the ticket AND the total amount of the movements.

    idticket    fecha   total   vuelto  hora    idtipocomp  idc     cae     fechavto    cajero  sesion  pago    sum(movimiento.importe)
1   fecha   145.89  0   hora    1   123     cae     2020-09-07  333     1   pago    145.89

    The strange thing is: you already have that value in the total row of the ticket because the normalization is not very good (well, you need to have the data there for some non-functional requirement). That's why it's not clear why you say you want to group by ticket AND show all movements . It is exclusionary.

    • 1