Question 113378 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select SUM of columns in unrelated tables in one query

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
Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search