{"id":90268,"date":"2021-03-15T17:00:12","date_gmt":"2021-03-15T17:00:12","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=90268"},"modified":"2021-03-27T11:56:26","modified_gmt":"2021-03-27T11:56:26","slug":"count-distinct-and-window-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/count-distinct-and-window-functions\/","title":{"rendered":"Count Distinct and Window Functions"},"content":{"rendered":"

Or: How to make magic tricks with T-SQL<\/p>\n

Starting our magic show, let\u2019s first set the stage:<\/p>\n

Count Distinct doesn\u2019t work with Window Partition<\/em><\/strong><\/p>\n

<\/p>\n

Preparing the example<\/h2>\n

In order to reach the conclusion above and solve it, let\u2019s first build a scenario. Using Azure SQL Database<\/strong>, we can create a sample database called AdventureWorksLT<\/em>, a small version of the old sample AdventureWorks<\/em> databases.<\/p>\n

Let\u2019s use the tables Product<\/em> and SalesOrderDetail<\/em>, both in SalesLT<\/em> schema. Each order detail row is part of an order and is related to a product included in the order. The product has a category and color.<\/p>\n

One interesting query to start is this one:<\/p>\n

SELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0ItemsPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>unitprice<\/span>\u00a0*<\/span>\u00a0orderqty<\/span>)<\/span>\u00a0AS<\/span>\u00a0Total<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0saleslt<\/span>.<\/span>salesorderdetail<\/span>\u00a0s<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productid<\/span>\u00a0=<\/span>\u00a0s<\/span>.<\/span>productid<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>\u00a0 <\/span><\/div>\n

This query results in the count of items on each order and the total value of the order.<\/p>\n

Let\u2019s add some more calculations to the query, none of them poses a challenge:<\/p>\n

SELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0ItemsPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>unitprice<\/span>\u00a0*<\/span>\u00a0orderqty<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0Total<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0productcategoryid<\/span>)<\/span>\u00a0CategoriesPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0color<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerOrder<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0saleslt<\/span>.<\/span>salesorderdetail<\/span>\u00a0s<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productid<\/span>\u00a0=<\/span>\u00a0s<\/span>.<\/span>productid<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>\u00a0 <\/span><\/div>\n

I included the total of different categories and colours on each order.<\/p>\n

Identifying the Problem<\/h2>\n

Now, let\u2019s imagine that, together this information, we also would like to know the number of distinct colours by category there are in this order.<\/p>\n

The group by only has the SalesOrderId.<\/em>\u00a0Due to that, our first natural conclusion is to try a window partition, like this one:<\/p>\n

SELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0ItemsPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>unitprice<\/span>\u00a0*<\/span>\u00a0orderqty<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0Total<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0productcategoryid<\/span>)<\/span>\u00a0\u00a0\u00a0CategoriesPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0color<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0color<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0partition<\/span>\u00a0BY<\/span>\u00a0productcategoryid<\/span>)<\/span>\u00a0ColorPerCategory<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0saleslt<\/span>.<\/span>salesorderdetail<\/span>\u00a0s<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productid<\/span>\u00a0=<\/span>\u00a0s<\/span>.<\/span>productid<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>\u00a0 <\/span><\/div>\n

\nOur problem starts with this query. Count Distinct is not supported by window partitioning<\/em><\/strong>, we need to find a different way to achieve the same result.<\/p>\n

Planning the Solution<\/h2>\n

We are counting the rows, so we can use DENSE_RANK<\/strong> to achieve the same result, extracting the last value in the end, we can use a MAX<\/strong> for that. This works in a similar way as the distinct count because all the ties, the records with the same value, receive the same rank value, so the biggest value will be the same as the distinct count.<\/p>\n

There are two ranking functions: RANK<\/strong> and DENSE_RANK<\/strong>. The difference is how they deal with ties.<\/p>\n

RANK:<\/strong> After a tie, the count jumps the number of tied items, leaving a hole.<\/p>\n

DENSE_RANK:<\/strong> No jump after a tie, the count continues sequentially<\/p>\n

The following query makes an example of the difference:<\/p>\n

SELECT<\/span>\u00a0productid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0color<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Rank<\/span>(<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER<\/span>\u00a0BY<\/span>\u00a0color<\/span>)<\/span>\u00a0[rank]<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dense_rank<\/span>(<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER<\/span>\u00a0BY<\/span>\u00a0color<\/span>)<\/span>\u00a0[dense_rank]<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>
\nWHERE<\/span>\u00a0\u00a0color<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>\u00a0 <\/span><\/div>\n

<\/p>\n

The new query using DENSE_RANK<\/strong> will be like this:<\/p>\n

SELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0ItemsPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>unitprice<\/span>\u00a0*<\/span>\u00a0orderqty<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0Total<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0productcategoryid<\/span>)<\/span>\u00a0CategoriesPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0color<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dense_rank<\/span>(<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0partition<\/span>\u00a0BY<\/span>\u00a0productcategoryid<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER<\/span>\u00a0BY<\/span>\u00a0color<\/span>\u00a0)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerCategory<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0saleslt<\/span>.<\/span>salesorderdetail<\/span>\u00a0s<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productid<\/span>\u00a0=<\/span>\u00a0s<\/span>.<\/span>productid<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>\u00a0 <\/span><\/div>\n

However, the result is not what we would expect:<\/p>\n

<\/p>\n

The groupby and the over clause don\u2019t work perfectly together. The fields used on the over clause need to be included in the group by as well, so the query doesn\u2019t work.<\/p>\n

Solving the Solution<\/h2>\n

The first step to solve the problem is to add more fields to the group by. Of course, this will affect the entire result, it will not be what we really expect. The query will be like this:<\/p>\n

SELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0productcategoryid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0ItemsPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>unitprice<\/span>\u00a0*<\/span>\u00a0orderqty<\/span>)<\/span>\u00a0AS<\/span>\u00a0Total<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerOrder<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dense_rank<\/span>(<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0partition<\/span>\u00a0BY<\/span>\u00a0salesorderid<\/span>,<\/span>\u00a0productcategoryid<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER<\/span>\u00a0BY<\/span>\u00a0color<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerCategory<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0saleslt<\/span>.<\/span>salesorderdetail<\/span>\u00a0s<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productid<\/span>\u00a0=<\/span>\u00a0s<\/span>.<\/span>productid<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0productcategoryid<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0color<\/span>\u00a0 <\/span><\/div>\n

There are two interesting changes on the calculation:<\/p>\n

    \n
  • CategoriesPerOrder<\/em> was removed, because the group by is some levels below this calculation, we can leave this for later.<\/li>\n
  • ColorPerOrder<\/em> is a fixed value, because we are grouping by colour.<\/li>\n<\/ul>\n

    We need to make further calculations over the result of this query, the best solution for this is the use of CTE \u2013 Common Table Expressions<\/strong>.<\/p>\n

    2nd<\/sup> Query Level<\/h3>\n

    The 2nd<\/sup> level of calculations will aggregate the data by ProductCategoryId<\/em>, removing one of the aggregation levels.<\/p>\n

    ;<\/span>WITH<\/span>\u00a0ranking<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0productcategoryid<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>*<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0ItemsPerOrder<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>unitprice<\/span>\u00a0*<\/span>\u00a0orderqty<\/span>)<\/span>\u00a0AS<\/span>\u00a0Total<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/span>(<\/span>DISTINCT<\/span>\u00a0color<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerOrder<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dense_rank<\/span>(<\/span>)<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OVER<\/span>\u00a0(<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0partition<\/span>\u00a0BY<\/span>\u00a0salesorderid<\/span>,<\/span>\u00a0productcategoryid<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORDER<\/span>\u00a0BY<\/span>\u00a0color<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColorPerCategory<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0saleslt<\/span>.<\/span>product<\/span>\u00a0p<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0saleslt<\/span>.<\/span>salesorderdetail<\/span>\u00a0s<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productid<\/span>\u00a0=<\/span>\u00a0s<\/span>.<\/span>productid<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0productcategoryid<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0color<\/span>)<\/span>
    \nSELECT<\/span>\u00a0salesorderid<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>itemsperorder<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0ItemsPerOrder<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>total<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Total<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CategoriesPerOrder<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>colorperorder<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0ColorPerOrder<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Max<\/span>(<\/span>colorpercategory<\/span>)<\/span>\u00a0ColorPerCategory<\/span>
    \nFROM<\/span>\u00a0\u00a0\u00a0ranking<\/span>
    \nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0salesorderid<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0productcategoryid<\/span>\u00a0 <\/span><\/div>\n

    The calculations on the 2nd<\/sup> query are defined by how the aggregations were made on the first query:<\/p>\n