How I can group by and count in PostgreSQL to prevent empty cells in result

Question

I have the table in PostgreSQL DB

Need to calculate SUM of counts for each event_type (example for 4 and 1) When I use query like this

SELECT account_id, date,
  CASE
    WHEN event_type = 1 THEN SUM(count)
    ELSE null
  END AS shows,
  CASE
    WHEN event_type = 4 THEN SUM(count)
    ELSE null
  END AS clicks
FROM widgetstatdaily WHERE account_id = 272 AND event_type = 1 OR event_type = 4 GROUP BY account_id, date, event_type ORDER BY date

I receive this table

With <null> fields. It's because I have event_type in select and I need to GROUP BY on it.

How I can make query to receive grouped by account_id and date result without null's in cells? Like (first row)

272   2018-03-28 00:00:00.000000    57    2

May be I can group it after receiving result

Gordon Linoff · Accepted Answer · 2018-07-26 13:37:16Z

2

You need conditional aggregation and some other fixes. Try this:

SELECT account_id, date,
       SUM(CASE WHEN event_type = 1 THEN count END) as shows,
       SUM(CASE WHEN event_type = 4 THEN count END) as clicks
FROM widgetstatdaily
WHERE account_id = 272 AND
      event_type IN (1, 4)
GROUP BY account_id, date
ORDER BY date;

Notes:

The CASE expression should be an argument to the SUM().
The ELSE NULL is redundant. The default without an ELSE is NULL.
The logic in the WHERE clause is probably not what you intend. That is fixed using IN.

answered Jul 26, 2018 at 13:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

Unreal Gordon. Our answers were even almost formatted the same way. You must get carpal tunnel regularly :-)

Mesbah Gueffaf · Accepted Answer · 2018-07-26 13:54:59Z

1

try its

SELECT account_id, date,
       SUM(CASE WHEN event_type = 1 THEN count  else 0 END) as shows,
       SUM(CASE WHEN event_type = 4 THEN count  else 0 END) as clicks
FROM widgetstatdaily
WHERE account_id = 272 AND
      event_type IN (1, 4)
GROUP BY account_id, date
ORDER BY date;

answered Jul 26, 2018 at 13:54

Mesbah Gueffaf

5581 gold badge7 silver badges23 bronze badges

Collectives™ on Stack Overflow

How I can group by and count in PostgreSQL to prevent empty cells in result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related