GryphonLeon
asked on
LEFT JOIN with SUM and IFNULL
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`,`s
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?
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`,`s
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!