Select SUM of columns in unrelated tables in one query-CodePudding

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

CodePudding user response：

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)?

CodePudding user response：

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.