PHP SQL Adding Column Values of Two Tables

[anitg]

I have two tables like below

tb1

Date          Cr     Dr
2018-04-29    100     50
2018-04-30    0      150
2018-05-01    250    100
2018-05-02    150    100
2018-06-10    300    250
2018-06-11    0       50

tb2

 Date          Cr     
2018-05-01    350    
2018-05-02    250    
2018-06-10    300    
2018-06-11    100
2018-06-15    200    
2018-06-18    100

I need the following Result

            Apr     May    Jun
Cr   100     1000   1000 
Dr   200     200    300

Basically I have to add the respective Cr columns from both table and get the Dr from tb1 for the corresponding dates and create a monthly report.

I have tried

SELECT Date(tb1.Date) as sDate, SUM(tb1.Cr+tb2.Cr) sumcr, SUM(tb1.Dr) sumdr FROM tb1,tb1 WHERE Date(tb1.Date)=Date(tb2.Date) GROUP BY YEAR(sDate), MONTH(sDate) ORDER BY Date(sDate) ASC

I have tried LEFT and INNER JOINs too like below

 SELECT *, SUM(tb1.Cr) as sumcr, SUM(tb1.Dr) as sumdr, SUM(tb2.Cr) as salecr  Date(tb1.Date) as sDate FROM tb1  LEFT JOIN tb2 ON  tb1.Date=tb2.Date  GROUP BY MONTH(tb1.Date) ORDER BY MONTH(tb1.Date) ASC

All these give wrong results.

Where am I going wrong ?

Any help will be greatly appreeciated !

[pty]

I'd do this in stages. Create a view where for each table you summarise totals by the month, you'll need to use extract or date_part to split the date into yearand month integer fields, which you'll group by, and sum to get the monthly totals. Then your joining of the two views becomes trivially easy.

Two simple steps are better than one complicated one.

[anitg]

Well, thanks for the suggestion @pty. I was under the impression that there would be a one query solution. Since there doesn't seem to be one, I guess I will have to use three queries.

Thanks again for showing the alternative way out.

[pty]

There most definitely is a one-query solution. You'd effectively have to do both stages of the route I suggested in one go, using subqueries and a union.

Using views to simplify the process is not really a compromise, that's what they are there for. If you don't want them to be mixed in with your everyday database objects, use a separate schema (again, that's what they are there for!)

The good thing about doing it in stages is that it makes debugging much easier. If your results look a bit iffy or don't perform well and you need to start deconstructing a huge query with subqueries in order to begin working out what's wrong, the additional complexity becomes a burden. If your final query is simple and the views that it relies on are simple it's easier to find the problem. Better still, when you fix or optimise it, all the things that rely on it will automatically improve.

[flashx4u]

This is how I would do it.
It's like inline views.
I don't like the hardcoded SQL Views, because this is much easier to programm and to update.

WITH tbData (Year, Month, Cr, Dr) AS (
    SELECT 
    YEAR([Date]) As [Year]
    , MONTH([Date]) AS [Month]
    , SUM([Cr]) AS [Cr]
    , SUM([Dr]) AS [Dr] 
    FROM tb1 
    GROUP BY YEAR([Date]), MONTH([Date]) 
UNION
    SELECT 
    YEAR([Date]) As [Year]
    , MONTH([Date]) AS [Month]
    , SUM([Cr]) AS [Cr]
    , 0 AS [Dr] 
    FROM tb2 
    GROUP BY YEAR([Date]), MONTH([Date]) 
)
SELECT 
     [Year]
    , [Month]
    , SUM([Cr]) AS [Cr]
    , SUM([Dr]) AS [Dr] 
    FROM tbData 
    GROUP BY [Year], [Month]

Hope this helps, Cheers.

[anitg]

@flashx4u thanks for the option. Just confused about the operator 'WITH'

[anitg]

I guess WITH is not an operator. Was it a typo ? I get error using that.

So I tried using SELECT there. BUT I get the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SELECT YEAR([Date]) As [Year] , MONTH([Date]) AS [Month] ' at line 1

[flashx4u]

Hello Anitg,
that was my fault, I wrote for MSSQL.

However, mysql V8.0 supports the WITH Syntax.
Click Here

With an earlier Version of MySQL I would suggest to use a View instead.
Basically put that statement from the WITH clause into a View like this:

MySQL-Syntax:

CREATE VIEW `aggregateddata` AS 
select year(`tb1`.`Date`) AS `Year`
,month(`tb1`.`Date`) AS `Month`
,sum(`tb1`.`Cr`) AS `Cr`
,sum(`tb1`.`Dr`) AS `Dr` 
from `tb1` 
group by year(`tb1`.`Date`)
,month(`tb1`.`Date`) 

union 
select year(`tb2`.`Date`) AS `Year`
,month(`tb2`.`Date`) AS `Month`
,sum(`tb2`.`Cr`) AS `Cr`
,0 AS `Dr` from `tb2` 
group by year(`tb2`.`Date`)
,month(`tb2`.`Date`);

And then you can use it the same way with a simple query:

select  `Year`
, `Month`
,sum(`Cr`) AS `Cr`
,sum(`Dr`) AS `Dr` 
from `aggregatedData` 
group by `Year`
,`Month`

Hope this helps,
greets, Helmut