Count rows obtained in a query with INNER JOIN in Mysql

Question

Count rows obtained in a query with INNER JOIN in Mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I have a fairly long query, if it works, but I do not know how to use COUNT to get how many rows are in total but not the information itself, query in question:

SELECT DISTINCT 
  tbl_docente.doc_cedula,
  tbl_docente.doc_nombre,
  tbl_docente.doc_apellido,
  tbl_docente.id_sede,
  tbl_docente.id_facultad,
  tbl_docente.id_carrera,
  tbl_publicacion.id_publicacion,
  tbl_publicacion.id_tipo_publicacion,
  tbl_publicacion.pub_titulo,
  tbl_publicacion.pub_nombre,
  tbl_publicacion.fuentebd,
  tbl_publicacion.pub_otros,
  tbl_publicacion.pub_sjr,
  tbl_publicacion.pub_codigo_lib_rev,
  tbl_publicacion.pub_seriado,
  tbl_publicacion.pub_seriado_dos,
  tbl_publicacion.pub_doi,
  tbl_publicacion.pub_url_public,
  tbl_publicacion.id_estado_public,
  tbl_publicacion.pub_rev_pares,
  tbl_publicacion.pub_filiacion,
  tbl_publicacion.pub_scaneo,
  tbl_publicacion.pub_scaneoa,
  tbl_publicacion.pub_fecha_envio,
  tbl_publicacion.pub_fecha_acepta,
  tbl_publicacion.pub_fecha_publicado,
  tbl_publicacion.pub_validado,
  tbl_publicacion.pub_estado
FROM
  tbl_docente
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_publicacion.pub_estado = 1 AND tbl_publicacion.pub_validado = 'SI'
GROUP BY
  tbl_publicacion.pub_titulo

mysql

asked by Roberth Cobeña 24.10.2018 в 03:05

source

3 answers

Sorting error heap sort descending autocomplete paste multiple elements

score 0 · Answer 1

I reduced the query to:

SELECT  DISTINCT 
  tbl_docente.id_sede,
  tbl_docente.id_facultad,
  tbl_publicacion.id_publicacion,
  tbl_publicacion.pub_sjr,
  tbl_publicacion.id_estado_public,
  tbl_publicacion.pub_validado
FROM
  tbl_docente
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_docente.id_sede = 1 AND tbl_publicacion.id_estado_public = 3 AND tbl_publicacion.pub_validado = 'SI' AND pub_sjr LIKE '' OR NULL
UNION
SELECT DISTINCT
  tbl_docente.id_sede,
  tbl_docente.id_facultad,
  tbl_publicacion.id_publicacion,
  tbl_publicacion.pub_sjr,
  tbl_publicacion.id_estado_public,
  tbl_publicacion.pub_validado
FROM
  tbl_docente
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_docente.id_sede = 1 AND tbl_publicacion.id_estado_public = 3 AND tbl_publicacion.pub_validado = 'SI' AND pub_sjr != '' OR NULL

With that I get a shorter and filtered table, now my problem remains the same, count the rows but not see them ...

This is the result I get, as you will see the query works, but I do not know how to put a COUNT

score 0 · Answer 2

accomplish my assignment, add a COUNT before the FROM but using lowercase and calling the header that will serve as an accountant, I hope it serves more people this experience I had.

 SELECT DISTINCT
  tbl_docente.id_sede,
  tbl_docente.id_facultad,
  tbl_publicacion.id_publicacion,
  tbl_publicacion.pub_sjr,
  tbl_publicacion.id_estado_public,
  tbl_publicacion.pub_validado,
  count(tbl_docente.id_sede) as totales
FROM
  tbl_docente 
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_docente.id_sede = 1 AND tbl_publicacion.id_estado_public = 3 AND tbl_publicacion.pub_validado = 'SI' AND pub_sjr LIKE '' OR NULL
UNION
SELECT DISTINCT
  tbl_docente.id_sede,
  tbl_docente.id_facultad,
  tbl_publicacion.id_publicacion,
  tbl_publicacion.pub_sjr,
  tbl_publicacion.id_estado_public,
  tbl_publicacion.pub_validado,
  count(tbl_docente.id_sede) as totales
FROM
  tbl_docente
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_docente.id_sede = 1 AND tbl_publicacion.id_estado_public = 3 AND tbl_publicacion.pub_validado = 'SI' AND pub_sjr != '' OR NULL

Result: the same 11 rows but counted at the end, 5 of one type and 6 of the other

score 0 · Answer 3

It turns out that the same solution can be reduced, better like this:

SELECT DISTINCT
  count(tbl_docente.id_sede) as totales
FROM
  tbl_docente 
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_docente.id_sede = 1 AND tbl_publicacion.id_estado_public = 3 AND tbl_publicacion.pub_validado = 'SI' AND pub_sjr LIKE '' OR NULL
UNION
SELECT DISTINCT
  count(tbl_docente.id_sede) as totales
FROM
  tbl_docente
INNER JOIN
  tbl_publicacion ON tbl_docente.id_docente = tbl_publicacion.id_docente
WHERE
  tbl_docente.id_sede = 1 AND tbl_publicacion.id_estado_public = 3 AND tbl_publicacion.pub_validado = 'SI' AND pub_sjr != '' OR NULL

In this case only this will come out (in my case better because I am generating some statistical graphics with Highcharts):