SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem with JOIN and ROLLUP syntax

    Hi there, I need your appreciated help.

    This is my query MySQL:
    Code:
    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:
    Code:
    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:
    Code:
    /*
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    when i run your query on the data provided, i get these results --
    Code:
    DTR  TOT  BUDGET
    S   15193  4400
    S   15193  4400
    however, when i make a very small but important change to your query, namely this --
    Code:
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Many thanks, I understand your lesson.
    Cheers


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •