Home / Questions / 445192
Accepted
Valentina
Asked: 2022-04-19 11:41:15 +0800 CST 2022-04-19 11:41:15 +0800 CST

How to count duplicate records and at the same time be able to print other data in PHP and MYSQL?

  • 772

I have the following record in my table logtrama:

id_logtrama    fechaHora    idCliente    idEquipo
    1          2021-04-18       20          8
    2          2021-04-18       20          8
    3          2021-04-18       20          8
    4          2021-04-18       20          1
    5          2021-04-18       4           4

And, in my table equipothe following:

idEquipo    idCliente     tipo
    1          20        Alarm1
    2          1         Alarm2
    3          2         Alarm3
    8          20        Alarm4

Now, through my query, I print results that match the given conditions and, without repeats:

$stmt = $con->prepare("SELECT l.id_logtrama,
                                    l.fechaHora,
                                    l.idCliente,
                                    l.idEquipo,
                                    l.statusGlobal
                                FROM logtrama l
                                JOIN equipo e
                                    ON l.idEquipo=e.idEquipo
                                    AND l.idCliente=e.idCliente
                                JOIN
                                    (SELECT MAX(t.id_logtrama) id_logtrama,
                                        COUNT(*) totals
                                    FROM logtrama t
                                    JOIN equipo e
                                        ON e.idEquipo = t.idEquipo
                                        AND e.idCliente = t.idCliente
                                    GROUP BY t.idcliente, t.idequipo) c
                                    ON c.id_logtrama = l.id_logtrama
                                WHERE DATE(fechaHora)=?");
$stmt->bind_param("s", $date_day);

In other words, it will no longer print the duplicates for me:

    1          2021-04-18       20          8
    2          2021-04-18       20          8
    3          2021-04-18       20          8
    4          2021-04-18       20          1

I will get the following result:

    3          2021-04-18       20          8
    4          2021-04-18       20          1

Already in PHP it would be:

    while ($stmt->fetch()) {
        echo 'ID logtrama: '.$id_logtrama.' Fecha: '.$fechaHora.' ID cliente: '.$pag_idCliente.' ID equipo: '.$pag_idEquipo.'';
    }

Departure:

ID logtrama: 3 Fecha: 2021-04-18 ID cliente: 20 ID equipo: 8
ID logtrama: 4 Fecha: 2021-04-18 ID cliente: 20 ID equipo: 1

So far so good, but I also need to be able to count the repeated records to print them, for example:

    1          2021-04-18       20          8
    2          2021-04-18       20          8
    3          2021-04-18       20          8
    4          2021-04-18       20          1

You can see 1, 2, 3they have repeated data, so how can I have the following data output:

ID logtrama: 3 Fecha: 2021-04-18 ID cliente: 20 ID equipo: 8 Total de registro repetidos: (3)
ID logtrama: 4 Fecha: 2021-04-18 ID cliente: 20 ID equipo: 1 Total de registro repetidos: (1)

In the same query, what conditions should I use to be able to print what I want to achieve.

php

2 Answers

  1. Best Answer

    I don't understand why you do another JOIN of the same two tables in a sub-query. Something like this should work:

                             SELECT 
                                MAX(l.id_logtrama) AS id_logtrama,
                                MAX(l.fechaHora) AS fechaHora,
                                l.idCliente,
                                l.idEquipo,
                                MAX(l.statusGlobal) AS statusGlobal,
                                COUNT(*) AS total
                            FROM logtrama l
                            JOIN equipo e
                                ON l.idEquipo=e.idEquipo
                                AND l.idCliente=e.idCliente 
                           WHERE DATE(l.fechaHora)=?
                           GROUP BY l.idcliente, e.idequipo
    • 0

  2. If you group by client, team, and date, the max frame id is what you're looking for, and the count comes out naturally:

    SELECT max(l.id_logtrama) 
     , max(l.fechaHora)
     , l.idCliente
     , l.idEquipo
     , count(1) duplicado
  FROM logtrama l
    JOIN equipo e
      ON    e.idEquipo  = l.idEquipo
        AND e.idCliente = l.idCliente
  WHERE date(l.fechaHora) = '2021-04-18'
  GROUP BY l.idCliente, l.idEquipo, date(l.fechaHora) 
  ORDER BY 1

    fiddler

    • 0