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:
Example data for LT_Clicks:
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:
LEFT JOIN (
SELECT link_id AS clinkid, IFNULL( SUM( clicks ) , 0 ) AS total_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
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?