Search code examples
mysqlsqldatabasegroup-bysubquery

Compute 2 subqueries then group by date

I have this Table

image attached

I want to run subqueries first then add them together grouped by date

Expected Result should be like this:

attached

I am running this query

(
SELECT DATE_FORMAT(dd1.modified_datetime,'%Y-%m-%d') as date, (v1+v2) as value FROM
    (SELECT modified_datetime, Sum(data->"$.amount") as v1
        FROM transactions
        GROUP BY modified_datetime) as dd1 ,            
    (SELECT modified_datetime, MAX(data->"$.amount") as v2
        FROM transactions
        GROUP BY modified_datetime) as dd2
        
    GROUP BY dd1.modified_datetime, value
)

and getting this result:

attached

Solution

  • Use JOIN between subqueries and every next one:

    (SELECT modified_datetime, Sum(data->"$.amount") as v1
    FROM transactions
    GROUP BY modified_datetime) as dd1 JOIN
(SELECT modified_datetime, MAX(data->"$.amount") as v2
    FROM transactions
    GROUP BY modified_datetime) as dd2 ON dd1.modified_datetime=dd2.modified_datetime