add a column to the select query which could be null-CodePudding

I would like to add another column to my select query called description inside posts table, the problem is that this value could be null. to make it clear, I have already linked foreign keys from tables Users, PostType and Votes. Attaching the query:

SELECT po.id,
       po.title,
       CONVERT(varchar, po.pDate, 104) AS pDate,
       pt.type,
       us.userName,
       SUM(CASE WHEN vt.isLike = 1 THEN 1 ELSE 0 END) AS upvotes,
       SUM(CASE WHEN vt.isLike = 0 THEN 1 ELSE 0 END) AS downvotes
FROM Posts po
     INNER JOIN PostType pt ON po.typeId = pt.id
     INNER JOIN Users us ON po.userId = us.id
     LEFT OUTER JOIN Votes vt ON vt.postId = po.id
GROUP BY po.id,
         po.pDate,
         po.title,
         pt.type,
         us.userName;

How to avoid group by null?

CodePudding user response：

You can make that column non-nullable on the fly. So in the SELECT clause just add one more column to display CASE WHEN po.description IS NULL THEN 'present' ELSE 'absent' END AS description and repeat it in the GROUP BY clause CASE WHEN po.description IS NULL THEN 'present' ELSE 'absent' END

SELECT po.id,
       po.title,
       CASE WHEN po.description IS NULL THEN 'present' ELSE 'absent' END AS description,
       CONVERT(varchar, po.pDate, 104) AS pDate,
       pt.type,
       us.userName,
       SUM(CASE WHEN vt.isLike = 1 THEN 1 ELSE 0 END) AS upvotes,
       SUM(CASE WHEN vt.isLike = 0 THEN 1 ELSE 0 END) AS downvotes
FROM Posts po
     INNER JOIN PostType pt ON po.typeId = pt.id
     INNER JOIN Users us ON po.userId = us.id
     LEFT OUTER JOIN Votes vt ON vt.postId = po.id
GROUP BY po.id,
         po.pDate,
         po.title,
         CASE WHEN po.description IS NULL THEN 'present' ELSE 'absent' END,
         pt.type,
         us.userName;