Merge two columns in result set

Hi guys,

I have two tables:

CREATE TABLE LT_Links (
      link_id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
      descr VARCHAR(35) DEFAULT NULL,
      dest_url VARCHAR(150) NOT NULL,
      clicks MEDIUMINT(8) UNSIGNED DEFAULT '0',
      PRIMARY KEY (link_id)
)

CREATE TABLE LT_Clicks (
      link_id INT(5) UNSIGNED NOT NULL,
      stats_year SMALLINT(4) UNSIGNED NOT NULL DEFAULT '2009',
      stats_month TINYINT(2) UNSIGNED ZEROFILL NOT NULL DEFAULT '01',
      clicks_thismonth MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
      PRIMARY KEY (link_id,stats_year,stats_month)
)

on which I do the following query:
SELECT l.*, itemCount.total_clicks FROM LT_Links l LEFT JOIN (SELECT link_id, SUM(clicks_thismonth) AS total_clicks FROM LT_Clicks GROUP BY link_id) AS itemCount ON l.link_id=itemCount.link_id

which returns:
link_id      descr      dest_url                              clicks      total_clicks
1            test1      http://www.domain.com            1            NULL
4            test4      http://www.domain2.com      NULL      1
3            test3      http://www.domain3.com      0            NULL

I'm looking for a way to merge the 'clicks' and 'total_clicks' results, so I can do an ORDER BY on the merged column.
In the merge, the NULL values should be stripped, so I would get the following results:

link_id      descr      dest_url                              clicks_merged
1            test1      http://www.domain.com            1
4            test4      http://www.domain2.com      1
3            test3      http://www.domain3.com      0

Is that possible?

Thanks in advance!
Leon
GryphonLeonAsked:
Who is Participating?
 
Terry WoodsIT GuruCommented:
Ok - we may need a coalesce on the subquery too, to give a 0 if it returns null. Try this:

select * from (
SELECT link_id, descr, dest_url, coalesce(clicks,0) + coalesce((select SUM(clicks_thismonth) FROM LT_Clicks where link_id = l.link_id),0) as clicks_merged
FROM LT_Links l
) as merge_select order by clicks_merged desc
0
 
Terry WoodsIT GuruCommented:
Maybe this? It sums the clicks and total_clicks (hopefully!):

select * from (
SELECT link_id, descr, dest_url, coalesce(clicks,0) + (select SUM(clicks_thismonth) FROM LT_Clicks where link_id = l.link_id) as clicks_merged
FROM LT_Links
) order by clicks_merged desc
0
 
GryphonLeonAuthor Commented:
Hi TerryAtOpus,

Thanks for your input. MySQL returned the following error:
#1248 - Every derived table must have its own alias

I've tried several things but couldn't find the fix for the error..
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Terry WoodsIT GuruCommented:
This should fix the error:

select * from (
SELECT link_id, descr, dest_url, coalesce(clicks,0) + (select SUM(clicks_thismonth) FROM LT_Clicks where link_id = l.link_id) as clicks_merged
FROM LT_Links
) as merge_select order by clicks_merged desc
0
 
GryphonLeonAuthor Commented:
Almost :) It gave the following error:
#1054 - Unknown column 'l.link_id' in 'where clause'

so, I added the l alias for the LT_Links table:

select * from (
SELECT link_id, descr, dest_url, coalesce(clicks,0) + (select SUM(clicks_thismonth) FROM LT_Clicks where link_id = l.link_id) as clicks_merged
FROM LT_Links l
) as merge_select order by clicks_merged desc

Which gave the following results (not fully correct):


link_id 	descr 	dest_url 		clicks_merged
4 		test4 	http://www.domain2.com 	1
1 		test1 	http://www.domain.com 	NULL
3 		test3 	http://www.domain3.com 	NULL

Open in new window

0
 
GryphonLeonAuthor Commented:
Works perfectly, thanks a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.