choose MAX sub-category if null Here is some made-up data as an example:




Category
Sub-Category
COUNT




Furniture
Chairs
25


Furniture
Null
15


Technology
Null
10


Technology
Phones
35




What I'm trying to do is... Keeping CATEGORY fixed, choose the non-null subcategory.
so this would be what would happen:




Category
Sub-Category
COUNT




Furniture
Chairs
25


Furniture
Chairs
15


Technology
Phones
10


Technology
Phones
35




and this would be the output:




Category
Sub-Category
COUNT




Furniture
Chairs
40


Technology
Phones
45




I figured out how to do this within Tableau, but I need to figure out how to do this in SQL.
Here is the solution inside Tableau:
IFNULL ([Sub-Category],
{FIXED [Category] : MAX([Sub-Category]) } )

Btw:  there is much more to why I'm doing this, and right now it looks like &quot;why would she want to do this?&quot;, but there is a reason that makes sense, but can't explain because I'm not allowed to show data from my job because it is confidential. The output would be a result of combining the like terms together.
Thank you!
With the data that I'm actually using (vs. the data in the example), I had to add in an additional field for the partition (not shown here), but it would look something like this...
CASE
  WHEN SUBCATEGORY IS NULL THEN
    FIRST_VALUE(SUBCATEGORY)
    OVER (PARTITION BY ADDITIONAL_FIELD,CATEGORY
          ORDER BY SUBCATEGORY NULLS LAST)
  ELSE
    SUBCATEGORY
END AS NEW_SUBCATEGORY

Full query:
SELECT
  ORDERID,
  CATEGORY,
  NEW_SUBCATEGORY,
  SUM(COUNTS) AS NEW_COUNTS
FROM
  (
    SELECT
      ORDERID,
      CATEGORY,
      SUBCATEGORY,
      CASE WHEN SUBCATEGORY IS NULL THEN FIRST_VALUE(SUBCATEGORY) OVER (PARTITION BY CATEGORY ORDER BY SUBCATEGORY NULLS LAST) ELSE SUBCATEGORY END AS NEW_SUBCATEGORY,
      COUNT(*) AS COUNTS
    FROM ORDER_TABLE
    GROUP BY
      ORDERID,
      CATEGORY,
      SUBCATEGORY
  )T
GROUP BY
  ORDERID,
  CATEGORY,
  NEW_SUBCATEGORY;