Combine multiple SQL queries into single result

Question

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

ash · Accepted Answer · 2021-11-25 05:57:50Z

1

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

edited Nov 25, 2021 at 5:57

answered Nov 25, 2021 at 5:47

ash

3,1353 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Squirrel · Accepted Answer · 2021-11-25 06:09:51Z

0

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

answered Nov 25, 2021 at 6:09

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Comments

Charlieface · Accepted Answer · 2021-11-25 09:55:46Z

0

You can use a LEFT JOIN with conditional aggregation

select
  s.saledate,
  sum(s.saleamt) as [Total Sales from All],
  sum(case when c.customerid is not null then s.saleamt end) as [Total Sales from Customers]
from Sales s
left join customers c on s.customerid = c.customerid
group by s.saledate;

answered Nov 25, 2021 at 9:55

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Combine multiple SQL queries into single result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related