How to count records of 3 tables linked by a foreign key?

Question

How to count records of 3 tables linked by a foreign key?

Navigation

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

2

I have a main table with one record, and in the other two tables there can be several records that depend on the record in table 1.

I need you to show me only 1 record as it appears in image 1, but adding 3 more columns corresponding to:

1 - Sum of the values of the 'valued' column in table 2 that coincide with the primary key of table 1 and the number of rows added.

2 - Total records in table 3 that match the primary key in table 1.

I have this sql but the sum does not work for me:

SELECT count(DISTINCT(p.idpagos)) as pagos, sum(valorabonado), count(DISTINCT(e.identrada)) FROM pagoperiodico pg
INNER JOIN pagos p USING(idpago)
INNER JOIN entrada e ON pg.idpago=e.idpago
WHERE pg.idpago=7334
GROUP BY pg.idpago

mysql sql postgresql-9.3

asked by Nelson Sepulveda 25.01.2018 в 06:51

source

2 answers

2

Dividing the question into parts:

1-Sum of the values of the 'valued' column in table 2 that coincide with the primary key of table 1 and the number of rows added

With the function SUM () you have the summation of 'valuebonded' and with the function COUNT () the number of rows added. Doing a JOIN of Table 1 and 2 you have the result:

SELECT COUNT(*), 
SUM(T2.valorabonado) 
FROM T1 JOIN T2 ON (T1.Id = T2.Id) 
WHERE T1.Id = 7334

2- Total records in table 3 that match the primary key in table 1.

This is also achieved with a COUNT (*):

SELECT COUNT(*) 
FROM T3 
WHERE T3.Id = 7334

And to 'package' everything in a cosulta, you can put the second query as a nested query. It would look like this:

SELECT COUNT(*) AS COL_NAME1, 
       SUM(T2.valorabonado) AS COL_NAME2, 
      (SELECT COUNT(*) FROM T3 WHERE T3.Id = 7334) AS COL_NAME3 
FROM T1 JOIN T2
ON (T1.Id = T2.Id) 
WHERE T1.Id = 7334

You would have to substitute the name of the tables (I used T1, T2 and T3), name the dynamic columns and in the SELECT add the columns you want to show from T1 (idpagoperiodico, name, start date, end date)

answered by 25.01.2018 в 08:40

Error trying to package a program with cx_Freeze Connecting Php with SQLServer 2000

score 0 · Accepted Answer

Well, since you have accepted the previous answer, I am certain that the key that relates the three tables is idpagoperiodico , so a possible solution could be:

SELECT  pg.idpagoperiodico,
        p.ValorAbonado,
        p.CantidadPagos,
        COUNT(e.identrada)               AS 'CantidadEntradas'
        FROM pagoperiodico pg
        LEFT JOIN (SELECT idpagoperiodico,
                      SUM(valorabonado)  AS 'ValorAbonado',
                      COUNT(1)           AS 'CantidadPagos'
                      FROM pagos
                      GROUP BY idpagoperiodico
                ) p
            ON p.idpagoperiodico = pg.idpagoperiodico
        LEFT JOIN entrada e
            ON e.idpagoperiodico = pg.idpagoperiodico
        GROUP BY pg.idpagoperiodico, 
                 p.ValorAbonado, 
                 p.CantidadPagos

Explanation:

The first LEFT JOIN points to a subquery that groups each idpagoperiodico and gets the sum of valorabonado and the number of records we have grouped in pagos .
The next LEFT JOIN points directly to entrada but there would be no problem with row repetitions since we are grouping at the end and counting the records in this table by COUNT(e.identrada)
The idea of using LEFT instead of INNER is simply to always have all the pagoperiodico.idpagoperiodico even if they do not have rows in the tables pagos and entrada , now if only you are interested in those cases where there is relationship with any of the two tables, simply modify the LEFT by INNER .

Note : I suggest you always indicate the appropriate database engine tag, in the question you indicated postgres and mysql , in this case I think the solution should work for both cases, but remember that SQL has language differences between the different engines.