[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Merge two columns in result set

Posted on 2009-04-22
6
Medium Priority
?
892 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:GryphonLeon
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24210351
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
 

Author Comment

by:GryphonLeon
ID: 24210414
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24210427
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:GryphonLeon
ID: 24210499
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
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1000 total points
ID: 24210515
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
 

Author Closing Comment

by:GryphonLeon
ID: 31573571
Works perfectly, thanks a lot!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question