CODEWITHSUNDEEP
sql-serverdatabase
Shaiwal Tripathi
Shaiwal Tripathi

Reputation: 627

How to combine result set of two different select statement in Sql Server

I have two different select statement but I want to show combined result in one query. I have following restriction.

  1. I can't use Join because it requires at-least one common column in both statements but in my case there is no common column.
  2. I can't user Union because it requires same structure of both statement and same no. of columns in both table. In my case both select statement has different structure.

Here are my two select statements.

select Center, SUM(PaidAmount) as TotalCollection, COUNT(Id) as TotalBills from DiagMain where 
        Cast(EntryDate as Date) = CONVERT(date, getdate()) group by Center order by Center desc
select Id, PtName, PaidAmount, DueAmount, Center, MachineName from DiagMain where Cast(EntryDate as Date) = 
        CONVERT(date, getdate()) order by Id desc

Please help guys...

Upvotes: 0

Views: 560

Answers (2)

Shishir Kushwaha
Shishir Kushwaha

Reputation: 163

If there are no common Columns to join results, you can use Row_Number() to combine the results.

    SELECT Table1.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY Center DESC) AS ROW, 
    Center, SUM(PaidAmount) AS Collection, COUNT(Id) AS TotalBills FROM DiagMain WHERE 
    CAST(EntryDate AS date) = CONVERT(date, GETDATE()) GROUP BY Center) Table1

    INNER JOIN

    (SELECT ROW_NUMBER() OVER (ORDER BY Id DESC) AS ROW, Id,  PtName, PaidAmount, 
    DueAmount, Center, MachineName FROM DiagMain WHERE CAST(EntryDate AS date) = 
    CONVERT(date, GETDATE())) Table2 

    ON Table1.ROW = Table2.ROW

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Looking at your query, it seems that you want the rows from DiagMains along with TotalCollection and TotalBills per Center in the result set. If that's the case, you can use window functions SUM() OVER and COUNT OVER():

SELECT
    Id,
    PtName,
    PaidAmount,
    DueAmount,
    Center,
    MachineName,
    TotalCollection = SUM(PaidAmount) OVER(PARTITION BY Center),
    TotalBIlls      = COUNT(Id) OVER(PARTITION BY Center)
FROM DiagMain
WHERE
    CAST(EntryDate AS DATE) = CAST(GETDATE() AS DATE)

Upvotes: 1

Related Questions