SQL query with GROUPING SETS to get subtotals with a HAVING clause (non additive measure)

All we need is an easy explanation of the problem, so here it is.

Let’s say I have a (fictional) table Order with the following columns:

OrderId: string

Supplier: string

Channel: string

I want a query that counts the number of distinct suppliers per channel, and overall. The query should consider only channels with a minimum of N distinct suppliers

create table #Order
(OrderId varchar(10),
Supplier varchar(10),
Channel varchar(10)
)

INSERT INTO #Order(OrderID,Supplier,Channel)
VALUES
 ('1','Supp 1','Paper')
,('2','Supp 2','Paper')
,('3','Supp 3','Paper')

,('6','Supp 4','Inter')
,('7','Supp 5','Inter')

,('13','Supp 1','aa')
,('14','Supp 2','aa')
,('15','Supp 3','aa')
,('55','Supp 4','aa')

SELECT ISNULL(Channel,'Total Distinct Supp')  as Channel, COUNT(DISTINCT Supplier) AS [SupplierCount]
FROM   #Order
GROUP  BY GROUPING SETS ((Channel), ())
HAVING COUNT(DISTINCT Supplier) > 2
ORDER  BY ISNULL(Channel,'Total Distinct Supp');

The problem with this query is that the grand total will include the suppliers from channel that have less than 2 suppliers.

Result from query above:

Channel SupplierCount
aa  4
Paper   3
Total Dist  5

Desired result:

Channel SupplierCount
aa  4
Paper   3
Total Dist  4

How can we amend the query above to fulfill the requirements?

I’m using sql azure…

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Just another way , with the same output:

create table #Order
(OrderId varchar(10),
Supplier varchar(10),
Channel varchar(10)
)

INSERT INTO #Order(OrderID,Supplier,Channel)
VALUES('1','Supp1','Paper')
,('2','Supp1','Paper')
,('3','Supp 3','Paper')
,('4','Supp 4','Paper')
,('5','Supp 5','Paper')

,('6','Supp 8','Inter')
,('7','Supp 6','Inter')

,('13','Supp 30','aa')
,('14','Supp 44','aa')
,('15','Supp 7','aa')
,('55','Supp 5','aa')


SELECT ISNULL(O.Channel,'Total Distinct Supp')  as Channel
      ,COUNT(Distinct Supplier) as SupplierCount
FROM #Order AS O
  INNER JOIN
  (
  SELECT Channel
       , COUNT(DISTINCT Supplier) AS [SupplierCount]
  FROM #Order as O
  GROUP BY Channel
  HAVING COUNT(Distinct Supplier)>2
 )C 
   ON o.Channel = C.Channel
GROUP BY GROUPING SETS ((O.Channel), ())
ORDER  BY ISNULL(O.Channel,'Total Distinct Supp');

output:

Channel SupplierCount
aa  4
Paper   4
Total Dist  7

dbfiddle

Method 2

You didn’t supply DDL, so the code below is not tested:
Maybe this is what you were looking for?

WITH GroupedSuppliers
    AS
    (
    SELECT Channel, COUNT(DISTINCT Supplier) AS [SupplierCount]
    FROM   [Order]
    GROUP  BY Channel
    HAVING COUNT(DISTINCT Supplier) > 10
    )
SELECT *
FROM   GroupedSuppliers
UNION ALL
SELECT  NULL, 
        COUNT(DISTINCT Supplier)
FROM [Order] 
WHERE Channel IN (SELECT Channel FROM GroupedSuppliers)
ORDER  BY Channel;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply