CODEWITHSUNDEEP
sqlpostgresql
Evgeny Gil
Evgeny Gil

Reputation: 327

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

I have the table in PostgreSQL DB

enter image description here

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

enter image description here

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

Upvotes: 0

Views: 295

Answers (2)

Mesbah Gueffaf
Mesbah Gueffaf

Reputation: 548

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Related Questions