How to get the SUM() of identical fields in multiple tables

[denisa]

Hi

I have three tables that are not related, each of them has a column named "cost".
I am able to get the SUM() for "cost" in each table. How could I get a total sum
of all three columns "cost" together as one result?

Thanks

[pbmods]

Heya, denisa. Welcome to TSDN!

The first solution that comes to mind would be to UNION the three tables as a subquery and find the SUM of that:

SELECT
        SUM(`cost`)
    FROM
    (
        (
            SELECT
                    `cost`
                FROM
                    `table1`
        )
        UNION
        (
            SELECT
                    `cost`
                FROM
                    `table2`
        )
        UNION
        (
            SELECT
                    `cost`
                FROM
                    `table3`
        )
    )
 

You may get an error about every derived table needing its own alias. Try this, and we'll go from there.

Though if the data in the tables is truly unrelated, I can't imagine the value of being able to sum them... perhaps the database needs a bit of a design tweak.

[denisa]

Thank you for the fast response. I do indeed get a "Every derived table must have its own alias" but with adding "as some_name" after the last FROM clause the message disappears. So again thank you, you saved me another couple of hours searching for the solution (probably not finding it anyhow).

Beginnings are hard when one doesn't get the principles/logic behind some things yet. And that is also the reason that my "database" for sure needs a big bit of a design tweak. Regarding wanting the sum of columns in unrelated tables - I'm using code from a PHP tutorial to list different kind of expenses per month and have a monthly total.

I decided for simplicity in the beginning phase, before knowing how to do it better, to have instead of one, three expenses tables. They contain partially different columns - eg. one is for gasoline with
id, date, title, description1, liter, cost
another one is for other expenses with
id, date, title, chain, description1, description2, cost
the third one similar but again a bit different.

I thought, if I would merge them, many fields would end up with no value and just take space. I just hope by reading further into those thick books I got, soon there will be the knowledge to make a design that makes more sense.

[pbmods]

Heya, Denisa.

It always starts out so easy. But nobody wants to live in a tiny house, and contractors are SO expensive....

There are several ways you could combine your tables, some more normalized than others.

Ideally, you'd want to split up your data into a few tables:

• An `expenses` table, with a numeric primary key, and amount and a date.
• An `expenses_attributes` table with a field name and field value, as well as a reference to the primary key from `expenses`
• You might also want an `expenses_categories` and `expenses_categories_map` table so that you can keep categorical data separate from 'memo' fields.

An an example, for gasoline, you might have an entry in `expenses`:
[id:102, date:2007-09-11 20:56:45, amount:45.00]

Then in the `expenses_attributes` table:
[id:102, field:quantity, value:12.85]

Then in `expenses_categories_map`:
[id:102, category:5]

And finally in `expenses_categories`:
[id:5, name:gasoline]

It would take some neat-looking joins to view all your data at once (or you could create some views), but then you could more reliably customize and summarize your data without worrying about data duplication or empty space.

If you wanted to get REALLY exciting (and *fun* to maintain), add a `vendors` table and add a reference to the vendor ID in your `expenses` table:
[id:102, date:2007-09-11 20:56:45, amount:45.00, vendor:86]

And in `vendors`:
[id:86, name:QuikTrip]

[EDIT: And don't forget about split transactions! How's that house coming? :) ]