Filtering by count column

Question

ProductGroup

ProductGroupID  Description  
301             Shampoo         
302             Soap
303             Brush

Product

ProductID   ProductGroupID   Desc        Price
4001        301              Shampoo1    2
4002        301              Shampoo2    3 
4003        301              Shampoo3    4
4004        302              Soap1       2
4005        302              Soap2       3
4006        302              Soap3       5
4007        303              Brush1      6 
4008        303              Brush2      7
4009        303              Brush3      8

Order

OrderID     ProductID  
50001       4001 
50002       4002 
50003       4002 
50004       4002 
50005       4003 
50006       4007 
50007       4009 
50008       4003 
50009       4001 
50010       4005 
50011       4008 
50012       4009 

Without Filtering

Select p.ProductID, p.Desc, pg.ProductGroupID, pg.Description  Count(o.ProductID) as OrderNum, p.Price
From Order o
inner join Product p
ON o.ProductID = p.ProductID
inner join ProductGroup pg
ON p.ProductGroupID = pg.ProductGroupID
Group By p.ProductID, p.Desc, pg.ProductGroupID, pg.Description, p.Price with Cube
Having ProductGroupID is not null 

ProductID  Desc        ProductGroupID  Description OrderNum   Price
4001       Shampoo1    301             Shampoo     2          2
4002       Shampoo2    301             Shampoo     3          3
4003       Shampoo3    301             Shampoo     2          4
--------   --------    301             Shampoo     7          ----
4004       Soap1       302             Soap        0          2
4005       Soap2       302             Soap        1          3
4006       Soap3       302             Soap        0          5
--------   --------    302             Brush       1          ----
4007       Brush1      303             Brush       1          6
4008       Brush2      303             Brush       1          7
4009       Brush3      303             Brush       2          8
--------   --------    303             Brush       4          ----

---

With filtering in subtotal

Select p.ProductID, p.Desc, pg.ProductGroupID, pg.Description  Count(o.ProductID) as OrderNum, p.Price
From Order o
inner join Product p
ON o.ProductID = p.ProductID
inner join ProductGroup pg
ON p.ProductGroupID = pg.ProductGroupID
Group By p.ProductID, p.Desc, pg.ProductGroupID, pg.Description, p.Price with Cube
Having ProductGroupID is not null 
AND SubTotal(OrderNum) =>  4 -- not working

ProductID  Desc        ProductGroupID  Description OrderNum   Price
4001       Shampoo1    301             Shampoo     2          2
4002       Shampoo2    301             Shampoo     3          3
4003       Shampoo3    301             Shampoo     2          4
--------   --------    301             Shampoo     7          ----
4007       Brush1      303             Brush       1          6
4008       Brush2      303             Brush       1          7
4009       Brush3      303             Brush       2          8
--------   --------    303             Brush       4          ----

How I will filter with their subtotals?

Answer 1

It looks like you are trying to filter an aggregated value. 1. Use HAVING instead of the last WHERE clause:

Select p.ProductID, p.Desc, pg.ProductGroupID, pg.Description  Count(o.ProductID) as OrderNum, p.Price
From Order o
inner join Product p
ON o.ProductID = p.ProductID
inner join ProductGroup pg
ON p.ProductGroupID = pg.ProductGroupID
Group By p.ProductID, p.Desc, pg.ProductGroupID, pg.Description, p.Price
HAVING COUNT(OrderNum) =>  4

Since this is for SQL 2008 R2, You can use WITH CUBE to get the subtotal:

Select 
    p.ProductID, 
    p.[Desc], 
    pg.ProductGroupID, 
    pg.Description, 
    Count(o.ProductID) as OrderNum, 
    p.Price
From 
    Order o
    inner join Product p ON o.ProductID = p.ProductID
    inner join ProductGroup pg ON p.ProductGroupID = pg.ProductGroupID
Group By 
    p.ProductID, 
    p.[Desc], 
    pg.ProductGroupID, 
    pg.Description, 
    p.Price
with cube

To grab a particular section based on some filter value you can do this:

SELECT * 
FROM 
    (
        Select 
            p.ProductID, 
            --p.[Desc], 
            --pg.ProductGroupID, 
            pg.Description, 
            Count(o.ProductID) as OrderNum, 
            p.Price
        From 
            Order o
            inner join Product p ON o.ProductID = p.ProductID
            inner join ProductGroup pg ON p.ProductGroupID = pg.ProductGroupID
        Group By 
            p.ProductID, 
            --p.[Desc], 
            --pg.ProductGroupID, 
            pg.Description, 
            p.Price
        with cube
    ) subTotals
    inner join 
    (
        SELECT Count(o.ProductID) as OrderNum, pg.Description
        FROM 
            Order o
            inner join Product p ON o.ProductID = p.ProductID
            inner join ProductGroup pg ON p.ProductGroupID = pg.ProductGroupID
        GROUP BY pg.Description
        HAVING Count(o.ProductID) >= 4
    ) groupFilter on groupFilter.Description = subTotals.description 
where Price IS NULL

Answer 2

Depending on which subtotals you want, you could either use the ROLLUP or CUBE operators.

Rollup will give you a hierachy of subtotals. (I am assuming that ProductID and Desc have a 1:1 match so I have concatenated them in the rollup, the same goes for the Group ID and description). I'm also assuming that you probably want products grouped within product groups.

GROUP BY ROLLUP((pg.ProductGroupID, pg.Description), (p.ProductID, p.Desc), p.Price)

Or you could use the cube operator to get all possible totals and subtotals.

GROUP BY p.ProductID, p.Desc, pg.ProductGroupID, pg.Description, p.Price WITH CUBE

You can also use GROUP BY GROUPING SETS(... to specify all the groupings you want manually.

There are further examples here.