Posted on Leave a comment

Grouping UNIONed Data

Calling all programmers. Soliciting your help! The following is a bunch of coding stuff. Click MORE to view post.

The following data is produced by a union of two queries. The goal would be to have the two rows for each month combined into a single row with the NULL value replaced with the appropriate count.

Month Year url impressions clickthrus
February 2006 1031 NULL
February 2006 NULL 101
January 2006 NULL 35
December 2005 NULL 25
December 2005 459 NULL
November 2005 NULL 80
November 2005 1121 NULL
October 2005 NULL 94
October 2005 1413 NULL
September 2005 1347 NULL
September 2005 NULL 98
August 2005 NULL 143
August 2005 1769 NULL
July 2005 NULL 204
July 2005 2026 NULL
June 2005 1887 NULL
June 2005 NULL 194
May 2005 NULL 148
May 2005 1713 NULL
April 2005 NULL 89
April 2005 64 NULL

The query is simple.

SELECT Month, Year, URL, COUNT(impressions), NULL
FROM impressionstable
GROUP BY URL, Year, Month


SELECT Month, Year, URL, NULL, COUNT(clickthrus)
FROM clickthrustable
GROUP BY URL, Year, Month
ORDER BY URL, Year, Month

So how do I combine the duplicate rows into a single row and eliminate NULL? (Excepting to keep NULL in the case that the month only appears once like January 2006 which had bad data and recorded no impressions despite having clickthrus)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.