How do I create multiple views in the same query in SQL and then join them?-CodePudding

This is the query that I am using. I need to join the three views to calculate the monthly total revenue. How should I proceed?

With Txn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE), '%y-%m') as Month, Sum(netPrice/100) as TransactionRevenue from transactions 
group by Month)

With Leaves as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval -1 MONTH), '%y-%m') as Month, sum(amount/100) as LeaveRevenue from driverPaymentTransactions
group by Month)

With Sxn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE ), '%y-%m') as Month, sum(amount/100) as SubscribedRevenue from subscribedDriversDailyRevenues
group by MONTH) 

Select * from Txn t
join Leaves l on t.Month = l.month
join Sxn s on t.month = s.month

CodePudding user response：

With Txn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE), '%y-%m') as Month, Sum(netPrice/100) as TransactionRevenue from transactions 
group by Month),
Leaves as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval -1 MONTH), '%y-%m') as Month, sum(amount/100) as LeaveRevenue from driverPaymentTransactions
group by Month),
Sxn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE ), '%y-%m') as Month, sum(amount/100) as SubscribedRevenue from subscribedDriversDailyRevenues
group by MONTH) 
Select * from Txn t
join Leaves l on t.Month = l.month
join Sxn s on t.month = s.month

CodePudding user response：

You need to "join" the subqueries

CREATE VIEw myview 
AS (With Txn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE), '%y-%m') as Month, Sum(netPrice/100) as TransactionRevenue from transactions 
group by Month)
, Leaves as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval -1 MONTH), '%y-%m') as Month, sum(amount/100) as LeaveRevenue from driverPaymentTransactions
group by Month)
, Sxn as(
Select DATE_FORMAT(DATE_ADD(createdAt, interval 330 MINUTE ), '%y-%m') as Month, sum(amount/100) as SubscribedRevenue from subscribedDriversDailyRevenues
group by MONTH) 

Select * from Txn t
join Leaves l on t.Month = l.month
join Sxn s on t.month = s.month)

CodePudding user response：

Do not join when you need to "pull" some different measures to the common attribute. Use union all, where you do not need to care about the most complete source of group values:

create table t1 as
select 1 as id, 10 as val union all
select 1, 20 union all
select 2, 30 union all
select 3, 49

create table t2 as
select 1 as id, 10 as val union all
select 3, 20 union all
select 3, 30 union all
select 5, 49

create table t3 as
select 4 as id, 10 as val union all
select 6, 20 union all
select 2, 30 union all
select 3, 49

with u as (
  select
    id
    , val as t1_val
    , cast(null as decimal) as t2_val
    , cast(null as decimal) as t3_val
  from t1

  union all

  select
    id
    , null as t1_val
    , val as t2_val
    , null as t3_val
  from t2

  union all

  select
    id
    , null as t1_val
    , null as t2_val
    , val as t3_val
  from t3
)
select
  id
  , sum(t1_val) as t1_val
  , sum(t2_val) as t2_val
  , sum(t3_val) as t3_val
from u
group by id

id | t1_val | t2_val | t3_val
-: | -----: | -----: | -----:
 1 |     30 |     10 |   null
 2 |     30 |   null |     30
 3 |     49 |     50 |     49
 5 |   null |     49 |   null
 4 |   null |   null |     10
 6 |   null |   null |     20

db<>fiddle here