CODEWITHSUNDEEP
mysqlsqlselectjoin
Logan
Logan

Reputation: 95

How do I get the number of rows in relation to another column?

I have three tables like the ones below

Applications:

+----+------+--------+
| id | name | author |
+----+------+--------+
|  1 | App1 |      1 |
|  2 | App2 |      1 |
|  3 | App3 |      1 |
+----+------+--------+

Installs:

+-----+----+---------------------+
| app | id | timestamp           |
+-----+----+---------------------+
|   3 |  1 | 2017-01-27 19:12:05 |
+-----+----+---------------------+

Users:

+----+----------+-------------------+--------------------------------------------------------------+
| id | username | email             | password                                                     |
+----+----------+-------------------+--------------------------------------------------------------+
|  2 | name     | [email protected] |                                                              |
+----+----------+-------------------+--------------------------------------------------------------+

Every time someone installs an app, it is added to the installs table and time stamped.

I know I can get individual app's installs by doing 

SELECT a.name,COUNT(b.id) as installs FROM applications a,installs b WHERE a.id=3 AND b.app=3;

And I'll get 

+------+----------+
| name | installs |
+------+----------+
| App3 |    30000 |
+------+----------+

What I would like is be able to get all the names of the apps, and the installs of the app from a certain author, in one result set like this:

+------+----------+
| name | installs |
+------+----------+
| App1 |    10000 |
| App2 |    20000 |
| App3 |    30000 |
+------+----------+

Upvotes: 2

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This is actually a basic GROUP BY query with a JOIN and WHERE:

SELECT a.name, COUNT(i.id) as installs
FROM applications a LEFT JOIN
     installs i
     ON a.id = b.app
WHERE a.author = 1
GROUP BY a.name;

Notes:

  • Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.
  • Your table aliases should be abbreviations for the table name, rather than random letters like b.
  • The JOIN condition uses columns from both tables.
  • The LEFT JOIN keeps all applications in for the author, even if there are no installs.

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93694

Use INNER JOIN without filter's

SELECT a.NAME,
       Count(*) AS installs
FROM   applications a
       JOIN installs i
         ON a.id = i.app

Upvotes: 2

Related Questions