Problem with JOIN and ROLLUP syntax

Databases
1

Hi there, I need your appreciated help.

This is my query MySQL:


SELECT 
   COALESCE(DTR, 'M') `DTR` 
, `TOT` 
, `BUDGET`
FROM 
(SELECT 
  A.DTR 'DTR'
, SUM(A.TOT) 'TOT' 
, CA.BUDGET 'BUDGET'
FROM tbl_A A
JOIN tbl_CA CA ON TRIM(A.DTR)=TRIM(CA.DTR)
WHERE 1 
GROUP BY 'DTR' WITH ROLLUP) x;

I need this output:


DTR	tot	budget
C	1.007	19.000                              
L	6.376	13.700                             
S	2.124	4.400
T	5.686	8.550
M	15.193	45.650

But WITH THIS QUERY the output is null, why?
Thanks in advance.

My tables MySQL:



/*
Navicat MySQL Data Transfer

Source Server         : _Mysql
Source Server Version : 50045
Source Host           : localhost:3306
Source Database       : db_

Target Server Type    : MYSQL
Target Server Version : 50045
File Encoding         : 65001

Date: 2011-08-29 11:24:59
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_CA`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_CA`;
CREATE TABLE `tbl_CA` (
  `xID` int(10) NOT NULL auto_increment,
  `DTR` varchar(10) default NULL,
  `BUDGET` int(10) default NULL,
  PRIMARY KEY  (`xID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_CA
-- ----------------------------
INSERT INTO tbl_CA VALUES ('1', 'T', '8550');
INSERT INTO tbl_CA VALUES ('2', 'L', '13700');
INSERT INTO tbl_CA VALUES ('3', 'C', '19000');
INSERT INTO tbl_CA VALUES ('4', 'S', '4400');


/*
Navicat MySQL Data Transfer

Source Server         : _Mysql
Source Server Version : 50045
Source Host           : localhost:3306
Source Database       : db_

Target Server Type    : MYSQL
Target Server Version : 50045
File Encoding         : 65001

Date: 2011-08-29 11:25:05
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_A`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_A`;
CREATE TABLE `tbl_A` (
  `WEEK` int(11) default NULL,
  `DTR` varchar(255) default NULL,
  `TOT` int(11) default NULL,
  `xID` int(10) NOT NULL auto_increment,
  PRIMARY KEY  (`xID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_A
-- ----------------------------
INSERT INTO tbl_A VALUES ('33', 'T', '5686', '1');
INSERT INTO tbl_A VALUES ('33', 'L', '6376', '2');
INSERT INTO tbl_A VALUES ('33', 'C', '1007', '3');
INSERT INTO tbl_A VALUES ('33', 'S', '2124', '4');
2

when i run your query on the data provided, i get these results –

DTR  TOT  BUDGET
S   15193  4400
S   15193  4400

however, when i make a very small but important change to your query, namely this –

SELECT 
   COALESCE(DTR, 'M') `DTR` 
, `TOT` 
, `BUDGET`
FROM 
(SELECT 
  A.DTR 'DTR'
, SUM(A.TOT) 'TOT' 
, CA.BUDGET 'BUDGET'
FROM tbl_A A
JOIN tbl_CA CA ON TRIM(A.DTR)=TRIM(CA.DTR)
WHERE 1 
GROUP BY DTR WITH ROLLUP) x;

then i get these results –

DTR  TOT   BUDGET
C    1007   19000
L    6376   13700
S    2124    4400
T    5686    8550
M   15193    8550

the lesson to be learned from this example is that you should not fool around with stupid quotes and backticks

3

Many thanks, I understand your lesson.
Cheers