Multi count from multi table in SQL

Question

I have the following table for example:

Table users:

|Id |User  |
| 1 |Frank |
| 2 |Tom   |
| 3 |Lisa  |

Table cities:

|Id |City  |
| 1 |Paris |
| 2 |Tokyo |

How can I have the count of each table users and cities in only one SQL query ?

I want to have:
- Number of users: 3
- Number of cities: 2

Thanks.

What results are you trying to get? Also, please add a tag for the specific RDBMS you're using (mysql, sql-server, Oracle, etc.). — Barmar
– Barmar, Commented May 31, 2014 at 14:10
Are you sure you wanted to use the word sum? Did you really mean count? — Justin Skiles
– Justin Skiles, Commented May 31, 2014 at 14:14

Barmar · Accepted Answer · 2014-05-31 14:20:31Z

2

Just guessing what you mean:

SELECT (SELECT COUNT(*) FROM users) AS user_count,
       (SELECT COUNT(*) FROM cities) AS city_count

In general, you can use a subquery any place where an expression is allowed, as long as the subquery returns 1 row with 1 column, by wrapping the subquery in parentheses.

edited May 31, 2014 at 14:20

answered May 31, 2014 at 14:11

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multi count from multi table in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related