CODEWITHSUNDEEP
mysqlsql
Usep10
Usep10

Reputation: 5

How to do arithmetic group by quarter mySQL?

I have 2 tables which have not relation both of them. 

Table of income
Id      Category    Nominal    Description         Date
----    --------    --------    --------         --------
1         ADD         10000           Q1        2020-03-05
2         DD          15000           Q2        2020-05-11
3         PAD          5000           Q3        2020-08-10

Table of outcome
Id      Category    Nominal    Description         Date
----    --------    --------    --------         --------
1         ADD          7000           Q1        2020-03-20
2         DD          10000           Q2        2020-06-02
3         PAD          2000           Q3        2020-08-28

So, I want to do subtraction of nominal from income with nominal from outcome group by quarter.

Here is my query :

CREATE view Total AS
    SELECT QUARTER(outcome.date) AS Qperiod, income.nominal-outcome.nominal AS remain
    FROM income, outcome
    GROUP BY YEAR(outcome.date), QUARTER(outcome.date)

this result shown below, it describe that first row in income table subtraction by all outcome nominal.

Qperiod    remain    
----      -------- 
1             3000
2                0
3             8000

Can anyone help me to solve this?

Upvotes: 0

Views: 68

Answers (1)

Nick
Nick

Reputation: 147196

You have no JOIN condition in your query, so each row of income gets matched to every row of outcome. Since you have no aggregation function, this effectively means that a random row from outcome is subtracted from each row of income. You should use modern, explicit JOIN syntax and put in the appropriate JOIN condition, which is that the year and quarter are the same in both tables:

CREATE view Total AS
    SELECT QUARTER(outcome.date) AS Qperiod, income.nominal-outcome.nominal AS remain
    FROM income
    JOIN outcome ON YEAR(outcome.date) = YEAR(income.date)
                AND QUARTER(outcome.date) = QUARTER(income.date)
    GROUP BY YEAR(outcome.date), QUARTER(outcome.date)

Output:

Qperiod     remain
1           3000
2           5000
3           3000

Demo on SQLFiddle

Upvotes: 1

Related Questions