[MySQL] Subquery

**cms9651** · Feb 18, 2012 04:08

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.

**r937** · Feb 18, 2012 05:05

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.

**cms9651** · Feb 18, 2012 05:17

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

**r937** · Feb 18, 2012 05:55

you need SUM(hhL+hhLI) 'tot hh L'

**cms9651** · Feb 18, 2012 06:02

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

**r937** · Feb 18, 2012 10:47

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

**cms9651** · Feb 18, 2012 11:54

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

**r937** · Feb 18, 2012 13:17

which figure is wrong? they look okay to me

**cms9651** · Feb 18, 2012 16:04

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

**r937** · Feb 18, 2012 16:11

have you correctly applied the SUM function in ~all~ the places where you should?

**cms9651** · Feb 18, 2012 16:29

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

**r937** · Feb 18, 2012 17:06

i will tell you only one more time -- you have to use the SUM function on all three columns

**cms9651** · Feb 19, 2012 03:10

Originally Posted by r937

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.

**r937** · Feb 19, 2012 06:33

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?

**cms9651** · Feb 19, 2012 15:54

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` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mySQLDate` date DEFAULT NULL,
  `zn` varchar(2) DEFAULT NULL,
  `description` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1;

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




DROP TABLE IF EXISTS `tbl_b`;
CREATE TABLE `tbl_b` (
  `zn` varchar(2) DEFAULT NULL,
  `znDescr` varchar(2) DEFAULT NULL,
  `hhL` int(10) DEFAULT NULL,
  `hhLI` int(10) DEFAULT NULL,
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_b
-- ----------------------------
INSERT INTO `tbl_b` VALUES ('4P', 'Sa', '12869', '8953', '4');
INSERT INTO `tbl_b` VALUES ('6M', 'Ca', '18105', '15270', '5');
INSERT INTO `tbl_b` VALUES ('6P', 'Ta', '14131', '12154', '8');

**r937** · Feb 19, 2012 16:49

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

**cms9651** · Feb 20, 2012 04:49

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):

tot hh L: 21822*2=43644
tot hh o: 12869*2=25738
tot hh i: 8953*2=17906

In the row #1 my output:

tot hh L: 21822
tot hh o: 12869
tot hh i: 8953

Last row in your output (not applicable for my case):

tot hh L: 170054
tot hh o: 94184
tot hh i: 75870

Last row in my output:

tot hh L: 81482
tot hh o: 45105
tot hh i: 36377

thanks for everything, but I try another way.
cheers

**r937** · Feb 20, 2012 04:56

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

**cms9651** · Feb 20, 2012 06:57

thanks a lot.

User Tag List

Thread: [MySQL] Subquery

Thread Tools

Display

[MySQL] Subquery

Bookmarks

Bookmarks

Posting Permissions