Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

LEFT JOIN with SUM and IFNULL

Posted on 2009-04-20
2
Medium Priority
?
797 Views
Last Modified: 2012-05-06
Hi all,

I have the following table setup:

CREATE TABLE `LT_Links` (
`link_id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`descr` VARCHAR(35) DEFAULT NULL,
`dest_url` VARCHAR(150) NOT NULL,
PRIMARY KEY (`link_id`))

CREATE TABLE `LT_Clicks` (
`link_id` INT(5) UNSIGNED NOT NULL,
`stats_year` TINYINT(2) UNSIGNED ZEROFILL NOT NULL DEFAULT '01',
`stats_month` TINYINT(2) UNSIGNED ZEROFILL NOT NULL DEFAULT '01',
`clicks` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`link_id`,`stats_year`,`stats_month`))

An entry in LT_Links would have 0 or more entries in LT_Clicks.

Example data for LT_Links:
row1: 1,'test1','http://www.domain.com'
row2: 2,'test2','http://www.domain2.com'

Example data for LT_Clicks:
row1: 1,09,04,30
row2: 1,09,05,40

Notice how the 2nd entry in LT_Links doesn't have an entry in LT_Clicks.

I would like to get a list of all entries in LT_Links, combined with a SUM of 'clicks' from LT_Clicks.

The following query:
----
SELECT *
FROM LT_Links
LEFT JOIN (
      SELECT link_id AS clinkid, IFNULL( SUM( clicks ) , 0 ) AS total_clicks
      FROM LT_Clicks
      GROUP BY link_id
) AS itemCount ON LT_Links.link_id = itemCount.clinkid
----

Produces the following output, which is almost what I need:
----
link_id      descr       dest_url                               clinkid       total_clicks
1             test1       http://www.domain.com       1             70
2             test2       http://www.domain2.com       NULL       NULL
----

Ideally, the column 'clinkid' would be removed from the results, as I don't need it. Also, the IFNULL doesn't seem to work, as 'total_clicks' shows NULL instead of 0.

Any idea what I'm doing wrong here?
0
Comment
Question by:GryphonLeon
2 Comments
 
LVL 10

Accepted Solution

by:
mahome earned 500 total points
ID: 24185590
The ifnull does work well, but it don't come into action as the ifnull is not executed, because there are no rows for id 2. The null values therefore come from the LEFT JOIN.

The following should work.

SELECT l.*, IFNULL(itemCount.total_clicks, 0) as total_clicks
FROM LT_Links l
LEFT JOIN (
      SELECT SUM( clicks ) AS total_clicks
      FROM LT_Clicks
      GROUP BY link_id
) AS itemCount ON LT_Links.link_id = itemCount.clinkid

Open in new window

0
 

Author Closing Comment

by:GryphonLeon
ID: 31572273
Thanks mahome,

Your solution gave an error at first, but with a small adjustment, it worked :)

-----
 SELECT l . * , IFNULL( itemCount.total_clicks, 0 ) AS total_clicks
FROM LT_Links l
LEFT JOIN (
SELECT link_id, SUM( clicks ) AS total_clicks
FROM LT_Clicks
GROUP BY link_id
) AS itemCount ON l.link_id = itemCount.link_id
-----

Thanks for your help and the explanation as well, I like knowing why it didn't work. Thanks again!
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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

578 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