CODEWITHSUNDEEP
mysqlsummarization
IgorAlves
IgorAlves

Reputation: 5556

How to summarize column price from 2 tables with no relationship?

In a mysql databse, I have tblA.price and tblB.price. There is no relationship between them.

I want summarize all sales from tableA and tableB. It will be something like that sum(tblA.price)+sum(tblB.price) AS total.

how could I perform that query?

Upvotes: 1

Views: 44

Answers (3)

IgorAlves
IgorAlves

Reputation: 5556

In order to complement the other answers, I had some issues when there is no result from one of the tables. It was returning null. For that reason I had to filter that result and turn it into 0. Just just did IFNULL(SUM(field),0).

Here is my final query:

SELECT 
   IFNULL(SUM(tblA.price),0) + (SELECT 
           IFNULL(SUM(fieldB),0)
       FROM
           tblB
       WHERE
           creation_date BETWEEN '$startDT' AND DATE_SUB(NOW(), INTERVAL 1 HOUR) AS amount
FROM
   tableA tblA
WHERE
   tblA.transaction_date BETWEEN 'startDT' AND DATE_SUB(NOW(),
       INTERVAL 1 HOUR)
       AND tblA.service_type <> 'service1'
       AND tblA.service_type <> 'service2'
       AND tblA.service_type <> 'service3';

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562911

The union that @cjsfj shows would work, and here are a couple of other options:

  1. Do two scalar subqueries and add them together.

    select (select sum(price) from tblA) + (select sum(price) from tblB) as total;

  2. Do two queries from your application, get the results of each, and add them together.

Upvotes: 1

cjsfj
cjsfj

Reputation: 29

Quick and Dirty. Unions aren't great. But if you have a fixed number of tables, this will work. Performance might get tricky, and it's definitely not pretty, but answers your question.

select sum(price) as totalprice from
(select sum(a.price) as price
from a
union all
select sum(b.price) as price
from b) as ab

Upvotes: 0

Related Questions