count database rows return null instead of 0

Question

When I'm trying to count how many likes each user has I do like this :

SELECT likes.LI_CNT liked_count
FROM `users` 
left outer JOIN (SELECT `user_id`, COUNT(*) LI_CNT
      FROM `likes`
      GROUP BY likes.`user_id`) likes ON users.`user_id` = likes.`user_id`

But this query return null values for those users who didn't like any tweet instead of returning zero! I tried to use this if statement but it returns the same result (with null values)

COUNT(IF(likes.user_id is null, 0, 1))

my tables schema:

CREATE TABLE IF NOT EXISTS `tweets` (
  `tweet_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reply_to_tweet_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `reply_to_user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `truncated` tinyint(1) NOT NULL,
  `author` varchar(30) NOT NULL,
  `text` varchar(255) NOT NULL,
  `media` varchar(255) NOT NULL,
  `entities` json NOT NULL,
  PRIMARY KEY (`tweet_id`)
)


CREATE TABLE IF NOT EXISTS `likes` (
  `user_id` varchar(50) NOT NULL,
  `tweet_id` varchar(50) NOT NULL,
  `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`tweet_id`)
)


CREATE TABLE IF NOT EXISTS `users` (
  `user_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `email` varchar(30) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(255) NOT NULL,
  `handelname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `account_url` varchar(255) NOT NULL,
  `bio` varchar(255) NOT NULL,
  `profile_pic` varchar(255) NOT NULL,
  `cover_pic` varchar(255) NOT NULL,
  `protected` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
)

how to get zero instead of null?

Gordon Linoff · Accepted Answer · 2020-01-06 18:54:39Z

3

COUNT(*) never returns NULL values. But LEFT JOIN does. If there is no match, then all columns -- including the count would be NULL.

One solution is to just use COALESCE() in the outer SELECT:

COALESCE(LI_CNT, 0) as LI_CNT

answered Jan 6, 2020 at 18:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

count database rows return null instead of 0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related