CODEWITHSUNDEEP
sql-server
TurboSonic
TurboSonic

Reputation: 1

Combine multiple SQL queries into single result

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

Upvotes: 0

Views: 559

Answers (3)

Charlieface
Charlieface

Reputation: 71144

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;

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 0

ash
ash

Reputation: 3085

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

Upvotes: 1

Related Questions