Search code examples
sql-servert-sqldataexplorer

SQL query to merge two aggregated subsets from the same table


I wrote a data.stackexchange query to find out what hour of the day a user posts questions and answers. You can see the SQL there. The results right now look like this:

hour hour questions answers 
---- ---- --------- ------- 
0    0    1         4       
null 2    null      4       
null 3    null      5       
null 4    null      7       
null 5    null      11      
null 6    null      10      
null 7    null      6       
null 8    null      1       
null 13   null      1       
null 14   null      7       
null 15   null      8       
null 16   null      11      
null 17   null      4       
null 18   null      10      
null 19   null      4       
null 20   null      6       
null 21   null      7       
22   22   1         6       
null 23   null      2     

How can I modify the query to:

  1. merge both hour columns into a single column, and
  2. if questions/answers are null, set to 0 instead.

Part 2 is low priority.

Edit: Here's the full SQL of the original query, since I'm about to improve it based on the answer:

SELECT
   q.hour, a.hour, questions, answers
FROM
(
   SELECT
     datepart(hour,creationdate) AS hour,
     count(*) AS questions
   FROM posts
   WHERE posttypeid=1 AND OwnerUserId=##UserID##
   GROUP BY datepart(hour,creationdate)
) q
FULL JOIN 
(
   SELECT
     datepart(hour,creationdate) AS hour,
     count(*) AS answers
   FROM posts
   WHERE posttypeid=2 AND OwnerUserId=##UserID##
   GROUP BY datepart(hour,creationdate)
) a
ON q.hour = a.hour
ORDER BY a.hour, q.hour

Solution

  • SELECT
       ISNULL(q.hour, a.hour) AS hour, 
       ISNULL(questions,0) AS questions, 
       ISNULL(answers,0) AS answers
    

    Or rewrite the query to get rid of the full join

       SELECT
         datepart(hour,creationdate) AS hour,
         count(CASE WHEN posttypeid = 1 THEN 1 END) AS questions,
         count(CASE WHEN posttypeid = 2 THEN 1 END) AS answers
       FROM posts
       WHERE posttypeid IN (1,2) AND OwnerUserId=##UserID##
       GROUP BY datepart(hour,creationdate)