TSQL Multi Level Group By

TSQL Multi Level Group By

Not so long ago i needed to do something with TSQL and that contained a multilevel query (3 levels of depth), but the standard usual way of doing things was not so good and didn't work, to most people that work with advanced SQL queries this is a piece of cake, but nevertheless someone might find this useful.

Example

Before going into SQL code let's see my very simple schema:

Now this is just a part of the schema but, this part is only needed for the example so no need to put more tables in here.

I wanted to get a brand grouped by the product count and count orders made on the products from that brand, simple... .

but doing just that this way.

SELECT BrandName, COUNT(*), SUM((SELECT COUNT(orderId) FROM OrderProduct WHERE ProductId = p.id)) FROM Product p JOIN Brand b
ON c.BrandId = b.Id GROUP BY BrandName

resulted in an error:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

The Solution?

SELECT BrandId, Count(Id) Products, SUM(ordersCnt) Orders FROM 
(
    SELECT p.BrandId, p.Id, (SELECT COUNT(orderId) FROM OrderProduct WHERE ProductId = p.id) ordersCnt FROM Product p
) AS OrdersPerBrand
GROUP BY BrandId

Now this works, to perform multi grouping in TSQL we need to create dynamic view tables so we could put the results in aggregate function without SQL Parser complaining, I'm sure there are some super valid reasons both architectural and logic to do it like this and not the way I imagined the first time, but it would be cool if the parser could figure out the first version and execute the code, this could simplify lots of nested groupings. I had just three here but consider 10? or groups in groups that have groups, such code would be a disaster.

This is a very short and simple post, as I try to write as often I can, because large architectural posts take lost of time so I rather be writing more frequently and from time to time post a big architectural topic with UML and full code.