How to get multiple COUNT(*) queries in different tables in one?

Question

I've been trying to get 4 or more COUNT(*) queries from different tables into one query.

Is this even possible or do I have to make four different queries?

This is my code:

try (final PreparedStatement sel = conn.prepareStatement("SELECT COUNT(*), SUM(coins) FROM User");
            final PreparedStatement sel2 = conn.prepareStatement("SELECT COUNT(*) FROM Friends");
            final PreparedStatement sel3 = conn.prepareStatement("SELECT COUNT(*) FROM Clans");
            final PreparedStatement sel4 = conn.prepareStatement("SELECT COUNT(*) FROM ClanMembers")) {
        try (final ResultSet rs = sel.executeQuery(); final ResultSet rs2 = sel2.executeQuery(); final ResultSet rs3 = sel3.executeQuery()) {
            rs.last();
            rs2.last();
            rs3.last();
            return new Object[] { rs.getInt(1), rs.getInt(2), rs2.getInt(1) / 2, rs3.getInt(1) };
        }
}

Using UNION ALL is the simplest solution to me but you have one query with 2 column ... so this will not work. so using sub-queries is one solution. FYI : You could use a Statement if you don't pass any parameter. — AxelH
– AxelH, Commented Sep 8, 2017 at 7:03

StanislavL · Accepted Answer · 2017-09-08 07:07:00Z

6

SELECT COUNT(*), SUM(coins) FROM User
union all
SELECT COUNT(*), 0 FROM Friends
union all
SELECT COUNT(*), 0 FROM Clans
union all
SELECT COUNT(*), 0 FROM ClanMembers
...

Amount and types of columns must be the same for all unioned queries

Answer updated to show how to combine the queries.

edited Sep 8, 2017 at 7:07

answered Sep 8, 2017 at 7:01

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

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

3 Comments

AxelH Over a year ago

Look the first query ;) there is a COUNT and a SUM. So your statement is actually telling he can't use this. But that match the quersion title ... so I guess this is valid ... I give you that !

Bob Over a year ago

Thanks! I noticed you added some zeros. Probably so the union works out. Will I have to ignore the zeros while going through the result set? Or won't they appear in my resultset?

StanislavL Over a year ago

To union queries they must have the same amount of columns and types. So if the first query has 2 columns then the second must also have 2 columns - that's why 0 value for the second column was added

Ronny Sulistio · Accepted Answer · 2017-09-08 07:27:45Z

4

Just give another optional solution (i can't comment yet so i have to write as answer)

StanislavL's answer is a nice answer, you can use it, it will return (n) row

but just in case if you need just 1 row you can try this

SELECT q1.count1,q1.coins,q2.count2, q3.count3, q4.count4
from (select (COUNT(*) count1, SUM(coins) coins, 1 id FROM User, 1 var) q1
inner join (SELECT COUNT(*) count2, 1 id FROM Friends) q2 on q1.id=q2.id
inner join (SELECT COUNT(*) count3, 1 id FROM Clans) q3 on q1.id=q3.id
inner join (SELECT COUNT(*) count4, 1 id FROM ClanMembers) q4 on q1.id=q4.id

answered Sep 8, 2017 at 7:27

Ronny Sulistio

1792 silver badges11 bronze badges

Comments

Bob · Accepted Answer · 2017-09-08 09:14:12Z

0

Well, it seems that I missed the obvious answer.

This solved my problem:

SELECT COUNT(*) FROM User union all SELECT COUNT(*) FROM Friends union all SELECT COUNT(*) FROM Clans union all SELECT COUNT(*) FROM ClanMembers union all SELECT SUM(coins) FROM User ...

edited Sep 8, 2017 at 9:14

answered Sep 8, 2017 at 8:42

Bob

1,2211 gold badge9 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to get multiple COUNT(*) queries in different tables in one?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related