Getting wrong COUNT values in query

Question

I am using the following query to get COUNT items from rows from the same table in LEFT JOIN.

This is the query:

SELECT 
pac.id_sat as id_sat,
pac.nombre_contacto as nombre_contacto,
pac.centro_contacto as centro_contacto,
pac.tel_contacto as tel_contacto,
pac.horario_contacto as horario_contacto,
pac.email_contacto as email_contacto,
pac.num_factura as num_factura,
pac.fecha_factura as fecha_factura,
eq.nombre_equipo as modelo_equipo,
pac.num_serie as num_serie,
pac.tipo_incidencia as tipo_incidencia,
pac.cod_sat as cod_sat,
pac.estado as estado,
pac.clinica as clinica,
pac.fecha_sat as fecha_sat,
COUNT(medfotos.id_media_sat) as num_fotos,
COUNT(medvideos.id_media_sat) as num_videos
FROM tb_sat pac 
LEFT JOIN tb_equipos eq ON pac.modelo_equipo = eq.id_equipo
LEFT JOIN tb_media_sat medfotos ON pac.cod_sat = medfotos.cod_sat AND  medfotos.tipo = 1
LEFT JOIN tb_media_sat medvideos ON pac.cod_sat = medvideos.cod_sat AND  medvideos.tipo = 2

WHERE pac.clinica = '".$idclinica."'

GROUP BY pac.id_sat

ORDER BY pac.fecha_sat DESC

My issue is that I am getting a wrong amount of COUNT items. The real value for num_fotos should be 3 and for num_videos should be 2.

I am getting num_fotos = 6 and num_videos = 6.

EDIT

Table tb_sat

Table tb_media_sat

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 23 '21 at 19:02
If you add table structure, then it will be better for getting answers. — Ishpreet, Aug 23 '21 at 19:05
@Ishpreet, done. tables structure is included as Edit in my question — mvasco, Aug 23 '21 at 19:09
Please share the table structure, sample input data, the expected output, and your attempts to resolve the problem — Nico Haase, Aug 23 '21 at 19:12
@NicoHaase, table structure is already shared, expected outpt is given, and my attempt to solve the issue is the given query — mvasco, Aug 23 '21 at 19:15
As usual, please don't share screenshots of data that can be shared best in text form. Also, you haven't shared sample data that could others help to reproduce the problem and fiddle around with a query — Nico Haase, Aug 23 '21 at 19:39

Ishpreet · Accepted Answer · 2021-08-23T19:55:29.483

1

Sub-query will work better in your case like as follows:

SELECT pac.*, (SELECT COUNT(id_media_sat) FROM tb_media_sat WHERE cod_sat=pac.cod_sat AND tipo=1) AS num_fotos, (SELECT COUNT(id_media_sat) FROM tb_media_sat WHERE cod_sat=pac.cod_sat AND tipo=2) AS num_videos FROM tb_sat pac WHERE pac.clinica = '".$idclinica."' ORDER BY pac.fecha_sat DESC

Rest columns, please add yourself slowly slowly. I hope you will get correct output.

edited Aug 23 '21 at 19:55

answered Aug 23 '21 at 19:17

Ishpreet

659
5
19

Tried and same output – mvasco Aug 23 '21 at 19:20
1

Can you show little bit data? Top 10 records of both tables? For first table, you can give us data of just 3-4 important columns, not whole table, like id_sat, nombre_contactto, cod_sat etc. – Ishpreet Aug 23 '21 at 19:33
There is no need for data. I am getting what is the problem with count. I will tell you proper solution – Ishpreet Aug 23 '21 at 19:39

Getting wrong COUNT values in query

1 Answers1