Small Tip - GROUP BY ALL

As you know, in many BI reports, what's missing is sometimes more interesting than what in the report. Therefore, in many cases I need to use OUTER JOINS to retrieve all data from the "dimension" table, even if there is no matching row.

I noticed not many are familiar with GROUP BY ALL which might be helpful.

As you can see in this simple example, the product 344 has only one color (silver). But I want to see all other colors in the table (red, yellow ...).

SELECT   P.Color, sum(unitprice) as SumUnitPrice

FROM dbo.FactInternetSales as F

     JOIN dbo.DimProduct as P

    ON P.ProductKey = F.ProductKey

WHERE P.ProductKey = 353

GROUP BY ALL  P.Color