Select SUM of columns in unrelated tables in one query

Question

I have these tables which are not related at all:

Books                          Apps                           Cars                       

Id     Views                   Id     Views                   Id     Views
------------                   ------------                   ------------
1      3                       4      5                       11      10
2      4                       5      100                     13      3
3      3                       6      5                       15      7



I want:

total_books    total_apps    total_cars
10             110           20

Instead of 3 separate queries for the sum of views, I would like to have 1 query where I will get the sum of views in each table

SELECT SUM(books.views), SUM(apps.views), SUM(cars.views) FROM books,apps,cars;

giving me NULL NULL NULL

What does the query you've written there give you? (Ignoring the 'giving me...' part; what's that about?) — simon at rcl, Oct 01 '22 at 13:26
@user20042973 I thought it was something general not relating to a specific DBS — Venda 97, Oct 01 '22 at 13:53
@Venda97 OK. (I didn't understand that that was the result, since it's formatted the same as the query I assumed it was part of the query and so asked about it. D'oh!) If the tables are empty then I would expect that result. Are you sure that there is data in the tables? — simon at rcl, Oct 01 '22 at 13:59
@simonatrcl Of course I do. I brought example that with having those data in tables, I get NULLs — Venda 97, Oct 01 '22 at 15:09

forever_learner · Accepted Answer · 2022-10-01T15:27:47.610

You would still need multiple selects since the tables are not related.

Try this (credits to this answer):

SELECT * FROM (SELECT SUM(Views) as total_books from Books) as Books, (SELECT Sum(Views) as total_apps from Apps) as Apps, (SELECT Sum(Views) as total_cars from Cars) as Cars

screenshot of my test

There are probably better/more performant ways to accomplish this, but at least it is a start.

Edit

Running your example

If I run the same command you did, the results are multiplied by 9 instead (see it here).

Most likely situation - empty table

I just realized your results are coming back as null. So as pointed out by others, your table must be empty. In my examples, I created the Views field as NOT NULL so I would never run into what you did.

see example here

Edit 2

It would be useful to provide additional information about where you are running your queries. Could you be accidentally running the queries against a different version of the tables (maybe a different context window in your software)?

Thank you, I realized that the reason for my query getting NULLs was not their emptiness but the qty of tables. I have got 8 of them each having column views with data inside them. When I reduce it to 3, I also get multiplied by 8 (as your example). — Venda 97, Oct 01 '22 at 15:28
Still, I have to admit that your answer is meeting my requirements and thanks for that. I will try to find some optimal solutions though. — Venda 97, Oct 01 '22 at 15:43

Rick James · Answer 2 · 2022-10-01T16:39:09.067

0

SELECT 
    (SELECT SUM(Views) from Books) as Books,
    (SELECT Sum(Views) from Apps)  as Apps,
    (SELECT Sum(Views) from Cars)  as Cars ;

(No need for 'derived' tables.)

Some databases (other than MySQL) may need FROM DUAL on the end.

edited Oct 01 '22 at 16:39

answered Oct 01 '22 at 16:05

Rick James

135,179
13
127
222

Is there any other more efficient and shorter solution? – Venda 97 Oct 01 '22 at 16:35
I pulled out a few unnecessary keystrokes; now I think this is the best by any metric. – Rick James Oct 01 '22 at 16:38

Select SUM of columns in unrelated tables in one query

2 Answers2

Edit

Running your example

Most likely situation - empty table

Edit 2