3

I got a database named accounts and account table inside of this database, Also I got the database named players and player table inside of this database.

How can I get a rows count of this two tables in one query?

I've tried this:

SELECT
    SUM(`account`.`account`.`id`) AS 'accounts',
    SUM(`player`.`player`) AS 'players';

But it doesn't work.

Cyclone
  • 14,839
  • 23
  • 82
  • 114

5 Answers5

2

A simple UNION operation on two select statements will do:

SELECT COUNT(*), 'Accounts' FROM Accounts.Account
UNION
SELECT COUNT(*), 'Players' FROM Players.Player

And you have to qualify each table with the database name since they're in separate databases.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
2

If you need exactly rows count (not sum), than do something like this:

select
(select count(*) from accounts.account) as count1,
(select count(*) from players.player) as count2

or

select count(*) as `count`,"account" as `table` from accounts.account
union all
select count(*) as `count`,"player" as `table` from players.player
Timur
  • 6,668
  • 1
  • 28
  • 37
1

Try:

SELECT
   COUNT(`account`.`id`) AS 'accounts',
   COUNT(`player`.`player`) AS 'players'
FROM
   `account`,
   `player`
Wes Crow
  • 2,971
  • 20
  • 24
1
SELECT COUNT(*) 
FROM (
    SELECT Id 
    FROM accounts.account 
    UNION ALL 
    SELECT player 
    FROM players.player ) AS BothTables
LoveAndCoding
  • 7,857
  • 2
  • 31
  • 55
Rabih Filfili
  • 11
  • 1
0
with Value (nbr, name ) as
(
select count(*) amount, 'AccountsCount' as ab from accounts..account
union all
select count(*) amount, 'PlayersCount' as ab from players..player
)
select * 
from value as s
PIVOT(sum(nbr) for name in (AccountsCount, PlayersCount) ) as pvt
Tristan
  • 3,301
  • 8
  • 22
  • 27
rabih filfili
  • 1
  • Please provide an explanation of how this code solves the question. This will help the OP learn from your answer as well as future searchers. – SnareChops Feb 01 '16 at 06:04