GROUP BY CASE statement

Question

I have a working query that is grouping data by hardware model and a result  but the problem is there are many  results    I have tried to reduce that down to  if result   0  then keep as 0  else set it to 1    This generally works  but I end up having       day            name        type   case   count ------------ ---------------- ------ ------ -------  2013-11-06   modelA              1      0     972  2013-11-06   modelA              1      1      42  2013-11-06   modelA              1      1       2  2013-11-06   modelA              1      1      11  2013-11-06   modelB              1      0     456  2013-11-06   modelB              1      1      16  2013-11-06   modelB              1      1       8  2013-11-06   modelB              3      0   21518  2013-11-06   modelB              3      1       5  2013-11-06   modelB              3      1       7  2013-11-06   modelB              3      1     563   Instead of the aggregate I am trying to achieve  where only 1 row per type case combo       day            name        type   case   count ------------ ---------------- ------ ------ -------  2013-11-06   modelA              1      0     972  2013-11-06   modelA              1      1      55  2013-11-06   modelB              1      0     456  2013-11-06   modelB              1      1      24  2013-11-06   modelB              3      0   21518  2013-11-06   modelB              3      1     575   Here is my query   select CURRENT DATE-1 AS day  model name  attempt type          CASE WHEN attempt result   0 THEN 0 ELSE 1 END          count     from attempt attempt  prod hw id prod hw id  model model where time  gt    2013-11-06 00 00 00    AND time  lt   2013-11-07 00 00 00  AND attempt hard id   prod hw id hard id AND prod hw id model id   model model id group by model name  attempt type  attempt result order by model name  attempt type  attempt result    Any tips on how I can achieve this would be awesome   Day will always be defined in the WHERE clause  so it will not vary  name  type  result case  and count will vary  In short  for any given model I want only 1 row per  type   case  combo  As you can see in the first result set I have 3 rows for modelA that have type 1 and case 1  because there are many  result  values that I have turned into 0 0 and anything else 1   I want that to be represented as 1 row with the count aggregated as in example data set 2

User · Accepted Answer

Your query would work already - except that you are running into naming conflicts or just confusing the output column (the CASE expression) with source column result, which has different content.

...
GROUP BY model.name, attempt.type, attempt.result
...

You need to GROUP BY your CASE expression instead of your source column:

...
GROUP BY model.name, attempt.type
       , CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END
...

Or provide a column alias that's different from any column name in the FROM list - or else that column takes precedence:

SELECT ...
     , CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END AS result1
...
GROUP BY model.name, attempt.type, result1
...

The SQL standard is rather peculiar in this respect. Quoting the manual here:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

And:

If an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY will interpret it as the output column name. This is the opposite of the choice that GROUP BY will make in the same situation. This inconsistency is made to be compatible with the SQL standard.

Bold emphasis mine.

These conflicts can be avoided by using positional references (ordinal numbers) in GROUP BY and ORDER BY, referencing items in the SELECT list from left to right. See solution below.
The drawback is, that this may be harder to read and vulnerable to edits in the SELECT list (one might forget to adapt positional references accordingly).

But you do not have to add the column day to the GROUP BY clause, as long as it holds a constant value (CURRENT_DATE-1).

Rewritten and simplified with proper JOIN syntax and positional references it could look like this:

SELECT m.name
     , a.type
     , CASE WHEN a.result = 0 THEN 0 ELSE 1 END AS result
     , CURRENT_DATE - 1 AS day
     , count(*) AS ct
FROM   attempt    a
JOIN   prod_hw_id p USING (hard_id)
JOIN   model      m USING (model_id)
WHERE  ts >= '2013-11-06 00:00:00'  
AND    ts <  '2013-11-07 00:00:00'
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

Also note that I am avoiding the column name time. That's a reserved word and should never be used as identifier. Besides, your "time" obviously is a timestamp or date, so that is rather misleading.

User · Answer

can you please try this  replace the case statement with the below one   Sum CASE WHEN attempt result   0 THEN 0 ELSE 1 END  as Count

User · Answer

For TSQL I like to encapsulate case statements in an outer apply  This prevents me from having to have the case statement written twice  allows reference to the case statement by alias in future joins and avoids the need for positional references   select oa day   model name   attempt type   oa result COUNT    MyCount  FROM attempt attempt  prod hw id prod hw id  model model WHERE time  gt    2013-11-06 00 00 00    AND time  lt   2013-11-07 00 00 00  AND attempt hard id   prod hw id hard id AND prod hw id model id   model model id OUTER APPLY       SELECT CURRENT DATE-1 AS day       CASE WHEN attempt result   0 THEN 0 ELSE 1 END result       oa     group by oa day   model name   attempt type   oa result order by model name  attempt type  oa result

User · Answer

Aliases can be used only if they were introduced in the preceding step  So aliases in the SELECT clause can be used in the ORDER BY but not the GROUP BY clause   Reference  Microsoft T-SQL Documentation for further reading   FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP   Hope this helps

User · Answer

Try adding the other two non COUNT columns to the GROUP BY   select CURRENT DATE-1 AS day   model name   attempt type   CASE WHEN attempt result   0 THEN 0 ELSE 1 END   count     from attempt attempt  prod hw id prod hw id  model model where time  gt    2013-11-06 00 00 00    AND time  lt   2013-11-07 00 00 00  AND attempt hard id   prod hw id hard id AND prod hw id model id   model model id group by 1 2 3 4 order by model name  attempt type  attempt result

[sql] GROUP BY + CASE statement

Examples related to sql

Examples related to postgresql

Examples related to group-by

Examples related to case

Examples related to aggregate-functions