Click here to Skip to main content
15,789,776 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am using 3 tables where group by id has to be implemented on first to tables
And not on 3rd table since the columns are different in 3rd table
but i should get the complete combination as one result set

Sample Data in Table 1

Id    Name    CreatedOn     SpentAmt
1     abc     2023/03/31      1000
2     Test    2023/03/31      14000
3     Mark    2023/03/31      2000
4     Robert  2023/03/31      700

Sample Data in Table 2

Id    Name     CreatedOn      UsedAmt
1     abc      2023/04/01      2000
7     Grace    2023/03/23      4000
2     Test     2023/03/15      10000
9     Mary     2023/03/10      1000

What I have tried:

Expected OutPut

Id   Name    SpentAmt     UsedAmt
1    abc      1000         2000
2    Test     14000        10000
3    Mark     2000         Null
4    Robert   700          Null
7    Grace    Null         4000
9    Mary     Null         1000

select id,
       spentamt as amt
FROM tbl1
where createdon >= '2021-04-01'
group by id,

select id,
       usedamt as amt
FROM tbl2
where createdon >= '2021-04-01'
group by id,
Updated 20-Apr-23 5:33am
raddevus 20-Apr-23 9:37am    
I can't tell what you want from your question. I don't see any reference to your 3rd table.
Why don't you try writing up working example at then add a link to your question?

1 solution

Seems like you're working on the same problem as this other user:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS with multiple queries[^]

You both have badly-structured data, and you both have terrible choice in table names.

The hacky workaround is virtually the same:
WITH cte As
    SELECT id, name FROM tbl1
    UNION SELECT id, name FROM tbl2
        SELECT Sum(SpentAmt)
        FROM tbl1 As S
        WHERE = And =
        And S.createdon >= '2021-04-01'
    ) As SpentAmt,
        SELECT Sum(SpentAmt)
        FROM tbl2 As S
        WHERE = And =
        And S.createdon >= '2021-04-01'
    ) As UsedAmt
    cte As N
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900