[MySQL] Subquery

cms9651 · February 18, 2012, 10:08am

Hi everyone, I need your help.

I try this query in MySQL:


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&lt;&gt;'it'
        GROUP BY A.zn
        WITH ROLLUP
) x;

And this is the output in MySQL:


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

Why in the last row of the output in the description zn I have Ta and not Tot ?
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:


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 · February 18, 2012, 11:05am

because in your subquery, the value of ‘description zn’ is undefined – try adding it to the GROUP BY clause
because you’re not summing them – it should say SUM(hhL) ‘tot hh o’ etc.

cms9651 · February 18, 2012, 11:17am

Thanks for your reply.

I try this query:

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&lt;&gt;'it'
        GROUP BY
        A.zn
      , B.znDescr
        WITH ROLLUP
) x;

But this is the output:

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 · February 18, 2012, 11:55am

you need [B]SUM/B ‘tot hh L’

cms9651 · February 18, 2012, 12:02pm

I’d appreciate your help so very much.

I try this query:

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&lt;&gt;'it'
        GROUP BY 
        A.zn
      , B.znDescr
        WITH ROLLUP
) x;

But this is the output… duplicates rows and all sum wrong :

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 · February 18, 2012, 4:47pm

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 · February 18, 2012, 5:54pm

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&lt;&gt;'it'
        GROUP BY description zn
        WITH ROLLUP
) x;

Not working the total sum of the columns singles in the last row…

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:

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 · February 18, 2012, 7:17pm

which figure is wrong? they look okay to me

cms9651 · February 18, 2012, 10:04pm

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 · February 18, 2012, 10:11pm

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

cms9651 · February 18, 2012, 10:29pm

I think… If try this in the query:


     , SUM(hhL) 'tot hh o'
     , SUM(hhLI) 'tot hh i'
     , (hhL+hhLI) 'tot hh L'

I have this new output:


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:


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:


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 · February 18, 2012, 11:06pm

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

cms9651 · February 19, 2012, 9:10am

Ok I understand and working with your suggestion, my query contains SUM and ROLLUP functions:

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&lt;&gt;'it'
        GROUP BY zn
        WITH ROLLUP
) x;

Output:

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:

hhL = 12869
hhLI = 8953
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 · February 19, 2012, 12:33pm

sigh…

what is this –

SELECT
       A.zn
     , B.znDescr 'description zn'
     , COUNT(*) 'tot inf'
     , [COLOR="#FF0000"]hhL
     , hhLI[/COLOR]

     , 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 · February 19, 2012, 9:54pm

I’d appreciate your help so very much.

I think having these values in output:


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:


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:


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 · February 19, 2012, 10:49pm

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&lt;&gt;'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 · February 20, 2012, 10:49am

I’m sorry… but I understand: my output it is not possible in this context.

Your query output:

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:

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 · February 20, 2012, 10:56am

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'
              , [COLOR="#FF0000"]ROUND(AVG(hhL+hhLI),0)[/COLOR] 'tot hh L'
              , [COLOR="#FF0000"]ROUND(AVG(hhL),0)[/COLOR] 'tot hh o'
              , [COLOR="#FF0000"]ROUND(AVG(hhLI),0)[/COLOR] 'tot hh i'
           FROM tbl_A A
           JOIN tbl_B B 
             ON A.zn = B.zn
          WHERE 1 
            AND YEAR(`mySQLDate`) = '2011'
            AND description&lt;&gt;'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 · February 20, 2012, 12:57pm

thanks a lot.