jump to navigation

NOTE: The spam filter is being unusually aggressive. If you comment does not immediately appear, it has simply been placed in moderation and I will approve it as quickly as possible. Thank you for your patience.

"Murphy was an optimist!"

Grouping UNIONed Data March 7, 2006 4:19 pm

Posted by Doug McCaughan in : Programming, Technology
, trackback

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 http://blog.siliconholler.com/ 1031 NULL
February 2006 http://blog.siliconholler.com/ NULL 101
January 2006 http://blog.siliconholler.com/ NULL 35
December 2005 http://blog.siliconholler.com/ NULL 25
December 2005 http://blog.siliconholler.com/ 459 NULL
November 2005 http://blog.siliconholler.com/ NULL 80
November 2005 http://blog.siliconholler.com/ 1121 NULL
October 2005 http://blog.siliconholler.com/ NULL 94
October 2005 http://blog.siliconholler.com/ 1413 NULL
September 2005 http://blog.siliconholler.com/ 1347 NULL
September 2005 http://blog.siliconholler.com/ NULL 98
August 2005 http://blog.siliconholler.com/ NULL 143
August 2005 http://blog.siliconholler.com/ 1769 NULL
July 2005 http://blog.siliconholler.com/ NULL 204
July 2005 http://blog.siliconholler.com/ 2026 NULL
June 2005 http://blog.siliconholler.com/ 1887 NULL
June 2005 http://blog.siliconholler.com/ NULL 194
May 2005 http://blog.siliconholler.com/ NULL 148
May 2005 http://blog.siliconholler.com/ 1713 NULL
April 2005 http://blog.siliconholler.com/ NULL 89
April 2005 http://blog.siliconholler.com/ 64 NULL

The query is simple.

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

UNION

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)

Comments after advertisement

Comments»

no comments yet - be the first?


trackback