How to use CASE statement without having to put it into GROUP BY

Question

I have a problem with my query. Here's my code:

select kpidate,reviewer as namareviewer, 
    count(formcode) as actual,
    round((0.2*count(formcode))) as target,
    CASE WHEN blibliknowledge !='' 
         THEN count(blibliknowledge)  
    END as blibli, 
    CASE WHEN solusi !=''
         THEN count(solusi)
    END as solusi
from kpi 
where kpidate >= '30/11/2015' AND kpidate<= '1/12/2015'
group by reviewer,kpidate,blibliknowledge,solusi

I have to add the columns blibliknowledge and solusi because of the CASE expression, and the output would be like this:

I want to group by namareviewer so 'elbert.lukman' will be grouped. So the output is just two rows. Column blibli and solusi will be calculated too as group by namareviewer.

@Shadow I thought Querying Language between both, it's the same. Isn't it? — Shieryn, Dec 01 '15 at 02:34
@Shieryn: Similar, but not the same by a long shot. Postgres is closer to standard SQL. — Erwin Brandstetter, Dec 01 '15 at 02:51

score 1 · Accepted Answer · answered Dec 01 '15 at 02:34

It looks like you want to do conditional aggregation. Use an expression that returns either 1 or 0 depending on the condition, and then wrap that in a SUM aggregate. Or, have an expression return NULL and non-null values, and wrap that in a COUNT expression.

In MySQL date literals should be supplied in 'yyyy-mm-dd' format.

As an example:

SELECT k.kpidate
     , k.reviewer                                               AS namareviewer
     , COUNT(k.formcode)                                        AS actual
     , ROUND((0.2*COUNT(k.formcode)))                           AS target
     , SUM(CASE WHEN k.blibliknowledge != '' THEN 1 ELSE 0 END) AS blibli
     , COUNT(CASE WHEN k.solusi != '' THEN 'foo' ELSE NULL END) AS solusi
  FROM kpi k
 WHERE k.kpidate >= '2015-11-30'
   AND k.kpidate <= '2015-12-01'
 GROUP
    BY k.reviewer
     , k.kpidate

In MySQL, we can shorthand the CASE expression with a boolean expression. MySQL returns integer value 1 if the expression evaluates to TRUE, and returns a 0 if the expression evaluates to FALSE. If the expression evaluates to NULL, then it returns a NULL.

SELECT k.kpidate
     , k.reviewer                              AS namareviewer
     , COUNT(k.formcode)                       AS actual
     , ROUND((0.2*COUNT(k.formcode)))          AS target
     , IFNULL(SUM(k.blibliknowledge != ''),0)  AS blibli
     , IFNULL(SUM(k.solusi          != ''),0)  AS solusi
  FROM kpi k
 WHERE k.kpidate >= '2015-11-30'
   AND k.kpidate <= '2015-12-01'
 GROUP
    BY k.reviewer
     , k.kpidate

Erwin Brandstetter · Answer 2 · 2021-01-12T17:13:06.037

You can shorten the syntax in Postgres as well. This works for Postgres and MySQL alike:

SELECT kpidate
     , reviewer                             AS namareviewer
     , count(formcode)                      AS actual
     , round(count(formcode) * 0.2)         AS target
     , count(blibliknowledge <> '' OR NULL) AS blibli
     , count(solusi <> '' OR NULL)          AS solusi
FROM   kpi
WHERE  kpidate >= '2015-11-30'
AND    kpidate <  '2015-12-01'  -- typically, you'd exclude that upper bound
GROUP  BY reviewer, kpidate;

In Postgres 9.4, you can use the SQL standard implementation of the aggregate FILTER clause:

SELECT kpidate
     , reviewer                                      AS namareviewer
     , count(formcode)                               AS actual
     , round(count(formcode) * 0.2)                  AS target
     , count(*) FILTER (WHERE blibliknowledge <> '') AS blibli
     , count(*) FILTER (WHERE solusi <> '')          AS solusi
FROM   kpi
WHERE  kpidate >= '2015-11-30'
AND    kpidate <  '2015-12-01'  -- typically, you'd exclude that upper bound
GROUP  BY 1, 2;  -- another optional syntax shortcut

Also demonstrating a syntax shorthand that's useful when you actually have to reference a longish expression in the SELECT list - to also answer your original question.

More:

How to use CASE statement without having to put it into GROUP BY

2 Answers2