Combine multiple SQL queries into single result-CodePudding

I'm trying to get the result of 2 queries into a single result set. I'm using SQL Server 2019 Express.

Here is the data I'm working with:

Table Sales

SaleDate	SaleAmt	CustomerID
11/1/2021	500	123
11/1/2021	100	234
11/1/2021	300	345
11/2/2021	500	456
11/2/2021	100	567
11/2/2021	200	678

Table Customers

CustomerID	CustomerName
123	Jon Doe
234	Jane Doe
456	Bob Doe
678	Jim Doe

Query #1:

select sales.saledate, sum(sales.saleamt) as 'Total Sales from All' 
from Sales 
group by sales.saledate

Query #2:

select sales.saledate, sum(sales.saleamt) as 'Total Sales from Customers'
from Sales 
where sales.customerid in (select customerid from customers) 
group by sales.saledate

This is my desired result:

SaleDate	Total Sales from All	Total Sales from Customers
11/1/2021	900	600
11/2/2021	800	700

CodePudding user response：

you can use join on the date of the sale

select s1.saledate, All_Total AS 'Total Sales from All', CustomersTotal as 'Total Sales from Customers'
from (
         select sales.saledate, sum(sales.saleamt) as All_Total
         from Sales
         group by sales.saledate
     ) s1
         inner join
     (
         select sales.saledate, sum(sales.saleamt) as CustomersTotal
         from Sales
         where sales.customerid in (select customerid from customers)
         group by sales.saledate
     ) s2 on s1.saledate = s2.saledate

CodePudding user response：

you can combine it in one single query using case expression.

select s.saledate, 
       sum(s.saleamt) as [Total Sales from All],
       sum(case when exists 
                     (
                         select * 
                         from   customers c 
                         where  c.customerid = s.customerid 
                     ) 
                then s.salesamt 
                end) as [Total Sales from Customers]
from   Sales s
group by s.saledate