SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [MySQL] Subquery

    Hi everyone, I need your help.

    I try this query in MySQL:
    Code:
    SELECT 	
           COALESCE(`Zn`,'Tot') `Zn`
        ,  COALESCE(`description zn`,'Tot') `description zn`
        , `tot inf`
        , `tot hh o`
        , `tot hh i`
        , `tot hh L`
        , ((`tot inf`/`tot hh L`)*1000)*0.9213 `Freq`
      FROM (SELECT
           A.zn
         , B.znDescr 'description zn'
         , COUNT(*) 'tot inf'
         , hhL 'tot hh o'
         , hhLI 'tot hh i'
         , (hhL+hhLI) 'tot hh L'
         , 'Freq' 
            FROM tbl_A A
            JOIN tbl_B B ON A.zn=B.zn
            WHERE 1 
            AND YEAR(`mySQLDate`) = '2011'
            AND description<>'it'
            GROUP BY A.zn
            WITH ROLLUP
    ) x;
    And this is the output in MySQL:
    Code:
    Zn	description zn	tot inf	tot hh o	tot hh i	tot hh L	Freq
    4P	Sa		2	12869		8953		21822		0,08443772
    6M	Ca		3	18105		15270		33375		0,08281348
    6P	Ta		1	14131		12154		26285		0,03504994
    Tot	Ta		6	14131		12154		26285		0.21030239
    1) Why in the last row of the output in the `description zn` I have `Ta` and not `Tot` ?
    2) Why for the columns `tot inf`, `tot hh o`, `tot hh i`, `tot hh L` and `Freq` I don't have the finally sum ?

    I need this output:
    Code:
    Zn	description zn	tot inf	tot hh o	tot hh i	tot hh L	Freq
    4P	Sa		2	12869		8953		21822		0,08443772
    6M	Ca		3	18105		15270		33375		0,08281348
    6P	Ta		1	14131		12154		26285		0,03504994
    ---	---		6	45132		36337		81482		0,23735154
    Can you help me?
    Many thanks, cheers.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    1. because in your subquery, the value of 'description zn' is undefined -- try adding it to the GROUP BY clause

    2. because you're not summing them -- it should say SUM(hhL) 'tot hh o' etc.
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply.

    I try this query:
    PHP Code:
    SELECT     
           COALESCE
    (`Zn`,'Tot') `Zn`
        ,  
    COALESCE(`description zn`,'Tot') `description zn`
        , `
    tot inf`
        , `
    tot hh o`
        , `
    tot hh i`
        , `
    tot hh L`
        , ((`
    tot inf`/`tot hh L`)*1000)*0.9213 `Freq`
      
    FROM (SELECT
           A
    .zn
         
    B.znDescr 'description zn'
         
    COUNT(*) 'tot inf'
         
    SUM(hhL'tot hh o'
         
    SUM(hhLI'tot hh i'
         
    , (hhL+hhLI'tot hh L'
         
    'Freq' 
            
    FROM tbl_A A
            JOIN tbl_B B ON A
    .zn=B.zn
            WHERE 1 
            
    AND YEAR(`mySQLDate`) = '2011'
            
    AND description<>'it'
            
    GROUP BY 
            A
    .zn
          
    B.znDescr
            WITH ROLLUP
    x
    But this is the output:

    Code:
    Zn	description zn	tot inf	tot hh o	tot hh i	tot hh L	Freq
    4P	Sa		2	25738		17906		21822		0.084437
    4P	Tot		2	25738		17906		21822		0.084437
    6M	Ca		3	54315		45810		33375		0.082813
    6M	Tot		3	54315		45810		33375		0.082813
    6P	Ta		1	14131		12154		26285		0.035050
    6P	Tot		1	14131		12154		26285		0.035050
    Tot	Tot		6	94184		75870		26285		0.210302
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you need SUM(hhL+hhLI) 'tot hh L'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'd appreciate your help so very much.

    I try this query:
    PHP Code:
    SELECT     
           COALESCE
    (`Zn`,'Tot') `Zn`
        ,  
    COALESCE(`description zn`,'Tot') `description zn`
        , `
    tot inf`
        , `
    tot hh o`
        , `
    tot hh i`
        , `
    tot hh L`
        , ((`
    tot inf`/`tot hh L`)*1000)*0.9213 `Freq`
      
    FROM (SELECT
           A
    .zn
         
    B.znDescr 'description zn'
         
    COUNT(*) 'tot inf'
         
    , (hhL'tot hh o'
         
    , (hhLI'tot hh i'
         
    SUM(hhL+hhLI'tot hh L'
         
    'Freq' 
            
    FROM tbl_A A
            JOIN tbl_B B ON A
    .zn=B.zn
            WHERE 1 
            
    AND YEAR(`mySQLDate`) = '2011'
            
    AND description<>'it'
            
    GROUP BY 
            A
    .zn
          
    B.znDescr
            WITH ROLLUP
    x
    But this is the output... duplicates rows and all sum wrong :

    Code:
    Zn	description zn	tot inf	tot hh o	tot hh i	tot hh L	Freq
    4P	Sa		2	12587		9546		44266		0.041625
    4P	Tot		2	12587		9546		44266		0.041625
    6M	Ca		3	19380		16181		106683		0.025907
    6M	Tot		3	19380		16181		106683		0.025907
    6P	Ta		1	14013		12908		26921		0.034222
    6P	Tot		1	14013		12908		26921		0.034222
    Tot	Tot		6	14013		12908		177870		0.031077
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you have three columns you should be summing -- 'tot inf', 'tot hh o', and 'tot hh i'

    if you look at the totals for these columns in post #3 and also in post #5, you will see that the correct totals are somewhere in there

    for example, 25738 + 17906 = 43644

    now go back and look at your query statement and figure out why

    it's all about the SUM()s, baby
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT     
           COALESCE
    (`Zn`,'Tot') `Zn`
        ,  
    COALESCE(`description zn`,'Tot') `description zn`
        , `
    tot inf`
        , `
    tot hh o`
        , `
    tot hh i`
        , `
    tot hh L`
        , ((`
    tot inf`/`tot hh L`)*1000)*0.9213 `Freq`
      
    FROM (SELECT
           A
    .zn
         
    B.znDescr 'description zn'
         
    COUNT(*) 'tot inf'
         
    , (hhL'tot hh o'
         
    , (hhLI'tot hh i'
         
    , (hhL+hhLI'tot hh L'
         
    'Freq' 
            
    FROM tbl_A A
            JOIN tbl_B B ON A
    .zn=B.zn
            WHERE 1 
            
    AND YEAR(`mySQLDate`) = '2011'
            
    AND description<>'it'
            
    GROUP BY description zn
            WITH ROLLUP
    x
    Not working the total sum of the columns singles in the last row...

    Code:
    Zn	description zn	tot inf	tot hh o	tot hh i	tot hh L	Freq
    4P	Sa		2	12587		9546		22133		0.084437
    6M	Ca		3	19380		16181		35561		0.082813
    6P	Ta		1	14013		12908		26921		0.035050
    
    6P	Tot		6	14013		12908		26921		0,205333
    My output:
    Code:
    Zn	description zn	tot inf	tot hh o	tot hh i	tot hh L	Freq
    4P	Sa		2	12587		9546		22133		0,084437
    6M	Ca		3	19380		16181		35561		0,082813
    6P	Ta		1	14013		12908		26921		0,035050
    						
    Tot	Tot		6	45980		38635		84615		0,202300
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    which figure is wrong? they look okay to me
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1 - Column `tot inf`: 2 + 3 +1 = 6 it's Ok;
    2 - Column `tot hh o`: 12587 + 19380 + 14013 = 14013 it's wrong (45980 it's ok);
    3 - Column `tot hh i`: 9546 + 16181 + 12908 = 12908 it's wrong (38635 it's ok);
    4 - Column `tot hh L`: 22133 + 35561 + 26921 = 26921 it's wrong (84615 it's ok);
    5 - Column `freq`: 0.084437 + 0.082813 + 0.035050 = 0.205333 it's wrong (0.202300 it's ok);
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    have you correctly applied the SUM function in ~all~ the places where you should?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think... If try this in the query:
    Code:
         , SUM(hhL) 'tot hh o'
         , SUM(hhLI) 'tot hh i'
         , (hhL+hhLI) 'tot hh L'
    I have this new output:
    Code:
    tot hh o	tot hh i	tot hh L
    58140		48543		35561
    25174		19092		22133
    14013		12908		26921
    97327		80543		26921
    But in the `tbl_B`:
    Code:
    INSERT INTO `tbl_B` (`zn`, `znDescr`, `hhL`, `hhLI`, `ID`) VALUES ('4P', 'Sa', 12869, 8953, 4);
    INSERT INTO `tbl_B` (`zn`, `znDescr`, `hhL`, `hhLI`, `ID`) VALUES ('6M', 'Ca', 18105, 15270, 5);
    INSERT INTO `tbl_B` (`zn`, `znDescr`, `hhL`, `hhLI`, `ID`) VALUES ('6P', 'Ta', 14131, 12154, 8);
    And the `tbl_A`:
    Code:
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (14, '2010-04-16', '6M', 'nto');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (15, '2010-05-20', '4P', 'nto');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (23, '2010-05-07', '4P', 'nto');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (30, '2011-08-19', '6M', 'nto');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (35, '2010-02-10', '6P', 'nto');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (40, '2011-07-06', '6M', 'ica');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (41, '2011-07-06', '6M', 'ica');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (42, '2011-07-11', '6P', 'ica');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (43, '2010-07-30', '6M', 'ale');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (70, '2011-04-04', '4P', 'ale');
    INSERT INTO `tbl_A` (`id`, `mySQLDate`, `zn`, `description`) VALUES (112, '2011-04-12', '4P', 'lio');
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i will tell you only one more time -- you have to use the SUM function on all three columns
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i will tell you only one more time -- you have to use the SUM function on all three columns
    Ok I understand and working with your suggestion, my query contains SUM and ROLLUP functions:
    PHP Code:
    SELECT     
           COALESCE
    (`Zn`,'Tot') `Zn`
        ,  
    COALESCE(`description zn`,'Tot') `description zn`
        , `
    tot inf`
        , `
    hhL`
        , `
    hhLI`
        , `
    tot hh L`
        , `
    tot hh o`
        , `
    tot hh i`
        , ((`
    tot inf`/`tot hh L`)*1000)*0.9213 `Freq`
      
    FROM (SELECT
           A
    .zn
         
    B.znDescr 'description zn'
         
    COUNT(*) 'tot inf'
         
    hhL
         
    hhLI

         
    sum(hhL+hhLI'tot hh L'
         
    sum(hhL'tot hh o'
         
    sum(hhLI'tot hh i'

         
    'Freq' 
            
    FROM tbl_A A
            JOIN tbl_B B ON A
    .zn=B.zn
            WHERE 1 
            
    AND YEAR(`mySQLDate`) = '2011'
            
    AND description<>'it'
            
    GROUP BY zn
            WITH ROLLUP
    x
    Output:

    Code:
    Zn	description zn	tot inf	hhL	hhLI	tot hh L	tot hh o	tot hh i	Freq
    4P	Sa		2	12869	8953	43644		25738		17906		0,04221857
    6M	Ca		3	18105	15270	100125		54315		45810		0,02760399
    6P	Ta		1	14131	12154	26285		14131		12154		0,03504994
    Tot	Ta		6	14131	12154	170054		94184		75870		0,03250531
    The problem is for example in the row #1:

    1) hhL = 12869
    2) hhLI = 8953
    3) tot hh L (hhL+hhLI) = the SUM is (12869 + 8953) >>>> 21822 ok?

    Instead in the `row # 1` I have total 46433 because the query multiplies the sum total of `tot hh L (hhL+hhLI)` for number `tot inf` = 21822x2 >>>> 46433.

    This is my problem.
    Be considered correct this output ?

    thanks for all.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sigh...

    what is this --
    Code:
    SELECT
           A.zn
         , B.znDescr 'description zn'
         , COUNT(*) 'tot inf'
         , hhL
         , hhLI
    
         , sum(hhL+hhLI) 'tot hh L'
         , sum(hhL) 'tot hh o'
         , sum(hhLI) 'tot hh i'
    you're expecting a couple of single values plus three sums?

    which single values do you think you'll get?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'd appreciate your help so very much.

    I think having these values in output:
    Code:
    Zn	description zn	tot inf		tot hh L	tot hh o	tot hh i	Freq
    4P	Sa		2		21822		12869		8953		0,08443715
    6M	Ca		3		33375		18105		15270		0,08281289
    6P	Ta		1		26285		14131		12154		0,03504994
    						
    Tot	---		6		81482		45105		36377		0.21030239

    With my query I have this output:
    Code:
    Zn	description zn	tot inf		tot hh L	tot hh o	tot hh i	Freq
    4P	Sa		2		21822		12869		8953		0,08443715
    6M	Ca		3		33375		18105		15270		0,08281289
    6P	Ta		1		26285		14131		12154		0,03504994
    
    Tot	Ta		6		26285		14131		12154		0,21030239

    I send the query and my tables:
    PHP Code:
    SELECT     
           COALESCE
    (`Zn`,'Tot') `Zn`
        ,  
    COALESCE(`description zn`,'Tot') `description zn`
        , `
    tot inf`
        , `
    tot hh L`
        , `
    tot hh o`
        , `
    tot hh i`
        , ((`
    tot inf`/`tot hh L`)*1000)*0.9213 `Freq`
      
    FROM (SELECT
           A
    .zn
         
    B.znDescr 'description zn'
         
    COUNT(*) 'tot inf'
         
    , (hhL+hhLI'tot hh L'
         
    , (hhL'tot hh o'
         
    , (hhLI'tot hh i'
         
    'Freq' 
            
    FROM tbl_A A
            JOIN tbl_B B ON A
    .zn=B.zn
            WHERE 1 
            
    AND YEAR(`mySQLDate`) = '2011'
            
    AND description<>'it'
            
    GROUP BY zn
            WITH ROLLUP
    x;




    DROP TABLE IF EXISTS `tbl_a`;
    CREATE TABLE `tbl_a` (
      `
    idint(10NOT NULL AUTO_INCREMENT,
      `
    mySQLDatedate DEFAULT NULL,
      `
    znvarchar(2) DEFAULT NULL,
      `
    descriptionvarchar(3) DEFAULT NULL,
      
    PRIMARY KEY (`id`)
    ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1;

    -- ----------------------------
    -- 
    Records of tbl_a
    -- ----------------------------
    INSERT INTO `tbl_aVALUES ('14''2010-04-16''6M''nto');
    INSERT INTO `tbl_aVALUES ('15''2010-05-20''4P''nto');
    INSERT INTO `tbl_aVALUES ('23''2010-05-07''4P''nto');
    INSERT INTO `tbl_aVALUES ('30''2011-08-19''6M''nto');
    INSERT INTO `tbl_aVALUES ('35''2010-02-10''6P''nto');
    INSERT INTO `tbl_aVALUES ('40''2011-07-06''6M''ica');
    INSERT INTO `tbl_aVALUES ('41''2011-07-06''6M''ica');
    INSERT INTO `tbl_aVALUES ('42''2011-07-11''6P''ica');
    INSERT INTO `tbl_aVALUES ('43''2010-07-30''6M''ale');
    INSERT INTO `tbl_aVALUES ('70''2011-04-04''4P''ale');
    INSERT INTO `tbl_aVALUES ('112''2011-04-12''4P''lio');




    DROP TABLE IF EXISTS `tbl_b`;
    CREATE TABLE `tbl_b` (
      `
    znvarchar(2) DEFAULT NULL,
      `
    znDescrvarchar(2) DEFAULT NULL,
      `
    hhLint(10) DEFAULT NULL,
      `
    hhLIint(10) DEFAULT NULL,
      `
    IDint(10NOT NULL AUTO_INCREMENT,
      
    PRIMARY KEY (`ID`)
    ENGINE=InnoDB AUTO_INCREMENT=DEFAULT CHARSET=latin1;

    -- ----------------------------
    -- 
    Records of tbl_b
    -- ----------------------------
    INSERT INTO `tbl_bVALUES ('4P''Sa''12869''8953''4');
    INSERT INTO `tbl_bVALUES ('6M''Ca''18105''15270''5');
    INSERT INTO `tbl_bVALUES ('6P''Ta''14131''12154''8'); 
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT COALESCE(`Zn`,'Tot') `Zn`
         , COALESCE(`description zn`,'Tot') `description zn`
         , `tot inf`
         , `tot hh L`
         , `tot hh o`
         , `tot hh i`
         , `tot inf`/`tot hh L` * 921.3 `Freq`
      FROM ( SELECT A.zn
                  , B.znDescr 'description zn'
                  , COUNT(*) 'tot inf'
                  , SUM(hhL+hhLI) 'tot hh L'
                  , SUM(hhL) 'tot hh o'
                  , SUM(hhLI) 'tot hh i'
               FROM tbl_A A
               JOIN tbl_B B 
                 ON A.zn = B.zn
              WHERE 1 
                AND YEAR(`mySQLDate`) = '2011'
                AND description<>'it'
             GROUP 
                 BY zn
               WITH ROLLUP ) x;
    
    Zn   description zn  tot inf  tot hh L  tot hh o  tot hh i   Freq
    4P   Sa               2        43644     25738     17906     0.04222
    6M   Ca               3       100125     54315     45810     0.02760
    6P   Ta               1        26285     14131     12154     0.03505
    Tot  Ta               6       170054     94184     75870     0.03251
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry... but I understand: my output it is not possible in this context.

    Your query output:
    Code:
    Zn   description zn  tot inf  tot hh L  tot hh o  tot hh i   Freq
    4P   Sa               2        43644     25738     17906     0.04222
    6M   Ca               3       100125     54315     45810     0.02760
    6P   Ta               1        26285     14131     12154     0.03505
    
    Tot  Ta               6       170054     94184     75870     0.03251
    I need this output:
    Code:
    Zn	description zn	tot inf	tot hh L	tot hh o	tot hh i	Freq
    4P	Sa		2	21822		12869		8953		0,08443715
    6M	Ca		3	33375		18105		15270		0,08281289
    6P	Ta		1	26285		14131		12154		0,03504994
    						
    Tot	---		6	81482		45105		36377		0.21030239
    The rows of my output is differents from rows in your output.

    In the row #1 your output (not applicable for my case):
    1. tot hh L: 21822*2=43644
    2. tot hh o: 12869*2=25738
    3. tot hh i: 8953*2=17906


    In the row #1 my output:
    1. tot hh L: 21822
    2. tot hh o: 12869
    3. tot hh i: 8953


    Last row in your output (not applicable for my case):
    1. tot hh L: 170054
    2. tot hh o: 94184
    3. tot hh i: 75870


    Last row in my output:
    1. tot hh L: 81482
    2. tot hh o: 45105
    3. tot hh i: 36377


    thanks for everything, but I try another way.
    cheers
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT COALESCE(`Zn`,'Tot') `Zn`
         , COALESCE(`description zn`,'Tot') `description zn`
         , `tot inf`
         , `tot hh L`
         , `tot hh o`
         , `tot hh i`
         , `tot inf`/`tot hh L` * 921.3 `Freq`
      FROM ( SELECT A.zn
                  , B.znDescr 'description zn'
                  , COUNT(*) 'tot inf'
                  , ROUND(AVG(hhL+hhLI),0) 'tot hh L'
                  , ROUND(AVG(hhL),0) 'tot hh o'
                  , ROUND(AVG(hhLI),0) 'tot hh i'
               FROM tbl_A A
               JOIN tbl_B B 
                 ON A.zn = B.zn
              WHERE 1 
                AND YEAR(`mySQLDate`) = '2011'
                AND description<>'it'
             GROUP 
                 BY zn
               WITH ROLLUP ) x;
    
    Zn   description zn  tot inf  tot hh L   tot hh o  tot hh i   Freq
    4P   Sa                2        21822      12869     8953     0.08444
    6M   Ca                3        33375      18105    15270     0.08281
    6P   Ta                1        26285      14131    12154     0.03505
    Tot  Ta                6        28342      15697    12645     0.19504
    you'll have to do the totals yourself with php or whatever
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •