COUNTING ON MYSQL RELATED TABLES

Navigation
1

I have two related tables ( Computers and Companies ).

Computers (the company column refers to the companies table.)

Companies

Using MYSQL, I want you to show me the result of how many computers there are in those companies.

  • Company A - > 30 Computers
  • Company B - > 23 Computers
  • Company C - > 11 Computers

I am new to MYSQL and in advance. THANKS:)

I've been trying something like this: 

SELECT empresas.name, computers.id
  FROM computers
 INNER JOIN empresas
    ON empresas.id = computers.id;

... but I do not know the final way to count the result with COUNT .

    
asked by EVM 27.11.2017 в 00:00
source

2 answers

1

Eduardo's response is good. The only thing is that companies would not appear without computers, if there are any. For these to appear in the result, you have to use a LEFT JOIN . And for the COUNT to be correct, you can not simply count the records ( COUNT(*) ), you have to count the records that include computer information ( COUNT(c.id) ): 

SELECT e.*, COUNT(c.id) AS computer_cnt
  FROM empresas e
  LEFT JOIN computers c
    ON c.empresa = e.id
 GROUP BY e.id
    
answered by 27.11.2017 / 02:25
source
1

According to the structure proposed, the following query would be made: 

SELECT Empresas.id, Empresas.name, COUNT(*) AS "# de Computadoras"
FROM Empresas
INNER JOIN Computadoras ON Computadoras.empresa = Empresas.id
GROUP BY Empresas.id, Empresas.name

The result according to the data of the graph would be the following:

    
answered by 27.11.2017 в 00:26
case when sql server in a single row Get full URL of the href