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');