CODEWITHSUNDEEP
mysqlsql
WY G
WY G

Reputation: 129

Question about COUNT (CASE WHEN AS 1) Returns to 1

SELECT DATE_TRUNC('week',occurred_at) AS week,
   COUNT (DISTINCT e.user_id) AS weekly_users,
   COUNT (DISTINCT CASE WHEN e.device IN  ('macbook pro','lenovo thinkpad','macbook air','dell inspiron notebook',
      'asus chromebook','dell inspiron desktop','acer aspire notebook','hp pavilion desktop','acer aspire desktop','mac mini')
      THEN e.user_id ELSE NULL END) AS computer,
   COUNT(DISTINCT CASE WHEN e.device IN ('iphone 5','samsung galaxy s4','nexus 5','iphone 5s','iphone 4s','nokia lumia 635',
   'htc one','samsung galaxy note','amazon fire phone') THEN e.user_id ELSE NULL END) AS phone,
    COUNT(DISTINCT CASE WHEN e.device IN ('ipad air','nexus 7','ipad mini','nexus 10','kindle fire','windows surface',
    'samsumg galaxy tablet') THEN e.user_id ELSE NULL END) AS tablet
    from tutorial.yammer_events e
   WHERE occurred_at > '2014-04-28' 
   GROUP by 1
   ORDER by 1

I am wondering why if I change the then e.user_id to then 1, the whole query returns to 1 instead of the count number, I am a little confused with the logic here.

Upvotes: 0

Views: 29

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Because COUNT(DISTINCT <expr>) counts the number of distinct non-NULL values.

The value 1 is constant. So there is only one value. Hence it returns 1.

If you use COUNT(1), then it would count the number of rows. However, this is more commonly written as COUNT(*).

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

If you change e.user_id to 1, then you should also change COUNT(DISTINCT ...) to SUM(...).

Upvotes: 1

Related Questions