CODEWITHSUNDEEP
sqlt-sqlsql-server-2012group-by
Techrocket9
Techrocket9

Reputation: 2066

How to group by an expression in TSQL and capture the result?

How can I include the results of an expression in a GROUP BY clause and also select the output of the expression ?

Say I have this table:

╔════════════════════════╦═══════════╦═══════╗
║         Forest         ║  Animal   ║ Count ║
╠════════════════════════╬═══════════╬═══════╣
║ Tongass                ║ Hyena     ║   600 ║
║ Tongass                ║ Bear      ║  1200 ║
║ Mount Baker-Snoqualmie ║ Wolf      ║    30 ║
║ Mount Baker-Snoqualmie ║ Bunny     ║     2 ║
║ Ozark-St. Francis      ║ Pigeon    ║   100 ║
║ Ozark-St. Francis      ║ Ostrich   ║     1 ║
║ Bitterroot             ║ Tarantula ║  9001 ║
╚════════════════════════╩═══════════╩═══════╝

I need a row with the count of carnivores in each forest and a row for the count of non-carnivores (if there are any). This is the output I'm looking for in this example:

╔════════════════════════╦═══════════════╦═══════════════╗
║         Forest         ║ AnimalsOfType ║ AreCarnivores ║
╠════════════════════════╬═══════════════╬═══════════════╣
║ Tongass                ║          1800 ║             1 ║
║ Mount Baker-Snoqualmie ║             2 ║             0 ║
║ Mount Baker-Snoqualmie ║            30 ║             1 ║
║ Ozark-St. Francis      ║           101 ║             0 ║
║ Bitterroot             ║          9001 ║             1 ║
╚════════════════════════╩═══════════════╩═══════════════╝

The information for whether or not an animal is carnivorous is encoded in the expression.

What I'd like to do is include the expression in the group-by and reference its result in the select clause:

SELECT TOP (1000)
    [Forest],
    SUM([COUNT]) AS AnimalsOfType,
    AreCarnivores
FROM [Tinker].[dbo].[ForestAnimals]
GROUP BY
    Forest,
    CASE WHEN ForestAnimals.Animal IN ('Pigeon', 'Ostrich', 'Bunny') THEN 0 ELSE 1 END AS AreCarnivores

However, this is not valid TSQL syntax.

If I include the Animal column in the GROUP BY clause to allow me to rerun the function in the SELECT, I'll get one row per animal type, which is not the desired behavior.

Doing separate selects into temp tables and unioning the results is undesirable because the real version of this query features a large number of expressions which need this behavior in the same result set, which would make for an extremely awkward stored procedure.

Upvotes: 1

Views: 91

Answers (1)

Ross Presser
Ross Presser

Reputation: 6255

Use a CTE:

WITH X AS (
  SELECT Forest, Animal, Count,
         CASE WHEN ForestAnimals.Animal IN ('Pigeon', 'Ostrich', 'Bunny') 
              THEN 0 
              ELSE 1 END AS AreCarnivores
  FROM [Tinker].[dbo].[ForestAnimals]
)
SELECT Forest, SUM(Count) AS AnimalsOfType, AreCarnivores
FROM X
Group by Forest, AreCarnivores;

Or be more verbose about it and repeat yourself:

SELECT   Forest, SUM(Count) AS AnimalsOfType, 
         CASE WHEN ForestAnimals.Animal IN ('Pigeon', 'Ostrich', 'Bunny') 
              THEN 0 
              ELSE 1 END AS AreCarnivores
FROM [Tinker].[dbo].[ForestAnimals]
GROUP BY Forest, CASE WHEN ForestAnimals.Animal IN ('Pigeon', 'Ostrich', 'Bunny') 
                 THEN 0 
                 ELSE 1 END;

They're equivalent queries to the optimizer.

Upvotes: 2

Related Questions