I have two queries. The output for the first query is the total sales quantity for all Brands and the output for the second query is the total sales quantity only for 'New' Brands. I need to create only one query (By merging below two queries: Query1 & Query2) where we can see the total sales of 'New' brands per Region, total sales of All brands per region and a new column named difference (Total sales quantity of All brands- Total sales quantity of New brands) side by side.

Expected Output :

These are my queries:

Query1:

SELECT InvoiceDate, Brand, Region, Quantity From TotalSales // For All Brands

Query2:

SELECT InvoiceDate, Brand, Region, Quantity From TotalSales where Brand='New' // For New Brands

CodePudding user response：

There are a couple of ways of doing this... First - I don't think you want the "Brand" column in your result. That doesn't make must sense. Also, I think you are going to want a summation for the AllBrands total...

1. Use subqueries

select allBrands.InvoiceDate, allBrands.Region, newBrands.Quantity as NewQuantity, allBrands.Quantity as allQuantity, allBrands.Quantity-newBrands.Quantity as Difference
FROM
(SELECT InvoiceDate, Region, SUM(Quantity) as Quantity From TotalSales GROUP BY InvoiceDate, Region) as allBrands
LEFT OUTER JOIN (SELECT InvoiceDate, Region, Quantity From TotalSales where Brand='New') as NewBrands ON NewBrands.InvoiceDate = allBrands.InvoiceDate AND NewBrands.Region = AllBrands.Region

or 2. use temp tables

SELECT InvoiceDate, Region, SUM(Quantity) as Quantity INTO #allSales From TotalSales GROUP BY InvoiceDate, Region;
SELECT InvoiceDate, Region, Quantity INTO #newSales From TotalSales where Brand='New';
select allBrands.InvoiceDate, allBrands.Region, newBrands.Quantity as NewQuantity, allBrands.Quantity as allQuantity, allBrands.Quantity-newBrands.Quantity as Difference
FROM #allBrands allBrands
LEFT OUTER JOIN #newBrands newBrands ON NewBrands.InvoiceDate = allBrands.InvoiceDate AND NewBrands.Region = AllBrands.Region;

CodePudding user response：

You want to get the quantity for brand = 'new' and the total quantity for all brands and compare the two.

One way to achieve this is conditional aggregation:

select
  invoicedate,
  'New' as brand,
  region,
  sum(case when brand = 'New' then quantity else 0 end) as qty_new,
  sum(quantity) as qty_all,
  sum(quantity) - sum(case when brand = 'New' then quantity else 0 end) as diff
from totalsales
group by invoicedate, region
having sum(case when brand = 'New' then quantity else 0 end) > 0
order by invoicedate, region;

Another is a join

with qnew as
(
  select invoicedate, brand, region, quantity
  from totalsales
  where brand = 'New'
)
, qall as
(
  select invoicedate, region, sum(quantity) as total
  from totalsales
  group by invoicedate, region
)
select
  qnew.*, qall.total, qall.total- qnew.quantity as diff 
from qnew
join qall on qall.invoicedate = qnew.invoicedate
         and qall.brand = qnew.brand
         and qall.region = qnew.region
order by qnew.invoicedate, qnew.brand, qnew.region;

CodePudding user response：

You can use simple conditional aggregation (SUM) on the data such as this:

DECLARE @TotalSales TABLE (InvoiceDate DATE, Brand NVARCHAR(16), Region NCHAR(1), Quantity INT)

INSERT INTO
    @TotalSales(
        InvoiceDate,
        Brand,
        Region,
        Quantity
    )
VALUES      ('10/1/2021', 'New', 'A', 20),
            ('10/1/2021', 'New', 'A', 20),
            ('10/1/2021', 'Old', 'A', 30),
            ('10/1/2021', 'Old', 'A', 30),
            ('10/1/2021', 'New', 'B', 10),
            ('10/1/2021', 'Old', 'B', 30),
            ('10/1/2021', 'Old', 'B', 50),
            ('10/1/2021', 'New', 'C', 50),
            ('10/1/2021', 'Old', 'C', 100),
            ('10/1/2021', 'New', 'D', 10),
            ('10/1/2021', 'New', 'D', 10),
            ('10/1/2021', 'New', 'D', 10),
            ('10/1/2021', 'Old', 'D', 100),
            ('10/1/2021', 'Old', 'D', 70),
            ('11/1/2021', 'Old', 'A', 50)

;WITH Data AS (
    SELECT
        ts.InvoiceDate,
        ts.Region,
        SUM(ts.Quantity) AS QuantityAll,
        SUM(CASE WHEN ts.Brand = 'New' THEN ts.Quantity ELSE 0 END) AS QuantityNew
    FROM
        @TotalSales ts
    GROUP BY
        ts.InvoiceDate,
        ts.Region
)

SELECT
    d.InvoiceDate,
    d.Region,
    d.QuantityAll,
    d.QuantityNew,
    d.QuantityAll - d.QuantityNew AS TheDifference
FROM
    Data d
ORDER BY
    d.InvoiceDate,
    d.Region

I used a CTE so that we don't have to repeat the conditional SUM(CASE WHEN... for subtracting between QuantityNew and QuantityAll.

Output is:

InvoiceDate   Region  QuantityAll QuantityNew TheDifference
2021-10-01    A       100         40          60
2021-10-01    B       90          10          80
2021-10-01    C       150         50          100
2021-10-01    D       200         30          170
2021-11-01    A       50          0           50

CodePudding user response：

select a.*,(a.Quantity-ISNULL(b.Quantity,0))as Difference  from (
SELECT InvoiceDate, Brand, Region, Quantity From TotalSales )a
LEFT OUTER JOIN(
SELECT  InvoiceDate, Brand, Region, Quantity From TotalSales where  Brand='New') B ON A.Region=b.Region and a.Brand=b.Brand

i think this query will help you