By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 985 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Mysql subquery COUNT in same Table returns NULL

h2odev
P: 3
Hi,
I have this MySQL table and I want to update multiple columns ( children, size) using COUNT() and SUM() on same table.

mytable

Expand|Select|Wrap|Line Numbers
  1. id  parentid  name      userid   path    children   privatesize    size
  2. =======================================================================
  3. 1   0         Test-1    1        NULL      5        20             125
  4. 2   0         Test-2    1        NULL      0        15             15
  5. 3   1         Test-3    1        /1/       3        25             75
  6. 4   1         Test-4    1        /1/       0        30             30
  7. 5   3         Test-5    1        /1/3/     0        10             10
  8. 6   3         Test-6    1        /1/3/     1        30             40
  9. 7   6         Test-7    1        /1/3/6/   0        10             10
  10. 8   0         Test-8    2        NULL      0        20             20
  11.  
Notes:
size = privatesize + children privatesize
NULL = "", just for demonstration

Let's update for now just one column, children column. Right now I use MySQL stored functions to calculate children:


Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`dbname`@`localhost` 
  4. FUNCTION `getchildren`( rowid INT, uid INT ) RETURNS INT(11)
  5.  
  6. BEGIN
  7.  
  8. DECLARE children    INT DEFAULT 0;
  9.  
  10. SELECT COUNT( `mytable`.`id` ) INTO children
  11. FROM `dbname`.`mytable`
  12. WHERE `mytable`.`path` LIKE CONCAT( '%/',rowid ,'/%' ) AND `mytable`.`userid` = uid;
  13.  
  14. RETURN  children;
  15. END
Test function getchildren:

Expand|Select|Wrap|Line Numbers
  1. SELECT dbname.getchildren( 1, 1 );
This returns number 5

To update rows ( Example id 2 and 5 ) I use this query:

Expand|Select|Wrap|Line Numbers
  1. UPDATE `dbname`.`mytable`
  2. SET `children` = getchildren( `mytable`.`id` , `mytable`.`userid` )
  3. WHERE `mytable`.`id` IN ( 2, 5 );
And works correctly.

But I don't want to use functions because later I need to update multiple columns (example: size ) and I don't want to call functions for every columns.

For that I have tried this query:

Expand|Select|Wrap|Line Numbers
  1. UPDATE `dbname`.`mytable` mt
  2. INNER JOIN  (
  3.         SELECT `mytable`.`path` AS path, COUNT( `mytable`.`id`)  AS countid
  4.         FROM `dbname`.`mytable`
  5.         GROUP BY `mytable`.`userid`
  6.         ) sub ON  `sub`.`path` LIKE CONCAT( "%/", `mt`.`id` , "/%" )
  7. SET `mt`.`children` = `sub`.`countid`
  8. WHERE `mt`.`id` IN ( 2, 5 );
In fact without success, this change the value of children to NULL.

Even the same method (using the same subquery logic) doesn't work in SELECT, returns NULL or returns multiple rows ( with correct count for each group) if I change GROUP BY: id or to path. It looks like COUNT() doesn't work like usual on subquery.

What I'm missing in this query? Can someone explain what really cause this behavior or I'm wrong at all?

Online SELECT Test

Thanks.
Oct 10 '14 #1

✓ answered by Luuk

The query with a jOIN, which 'determines' the new value for childeren is:
Expand|Select|Wrap|Line Numbers
  1. SELECT  mt.*, count(*) CNT  
  2. from mytable mt, mytable mtc 
  3. where mtc.path like concat('%',mt.id,'%') 
  4. group by mt.id;
Converted into an update:
Expand|Select|Wrap|Line Numbers
  1. UPDATE mytable m
  2. LEFT JOIN (
  3.     SELECT  mt.id, count(*) CNT  
  4.     from mytable mt, mytable mtc 
  5.     where mtc.path like concat('%',mt.id,'%') 
  6.     group by mt.id) m2 on m2.id=m.id
  7. SET m.children=ifnull(CNT,0);

Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,031
Sorry, i am not explaining what you do wrong,
but maybe this helps:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   mt.*, 
  3.   (select count(*) 
  4.    from mytable mtc 
  5.    where path like concat('%',mt.id,'%')) CNT 
  6. FROM mytable mt ;
this returns:
Expand|Select|Wrap|Line Numbers
  1. +----+----------+--------+--------+---------+----------+------+-------------+------+
  2. | id | parentid | name   | userid | path    | children | size | privatesize | CNT  |
  3. +----+----------+--------+--------+---------+----------+------+-------------+------+
  4. |  1 |        0 | Test-1 |      1 |         |        5 |   20 |          95 |    5 |
  5. |  2 |        0 | Test-2 |      1 |         |        0 |   15 |          15 |    0 |
  6. |  3 |        1 | Test-3 |      1 | /1/     |        3 |   25 |          75 |    3 |
  7. |  4 |        1 | Test-4 |      1 | /1/     |        0 |   30 |          30 |    0 |
  8. |  5 |        3 | Test-5 |      1 | /1/3/   |        0 |   10 |          10 |    0 |
  9. |  6 |        3 | Test-6 |      1 | /1/3/   |        1 |   30 |          40 |    1 |
  10. |  7 |        6 | Test-7 |      1 | /1/3/6/ |        0 |   10 |          10 |    0 |
  11. |  8 |        0 | Test-8 |      2 |         |        0 |   20 |          20 |    0 |
  12. +----+----------+--------+--------+---------+----------+------+-------------+------+
  13.  
Oct 11 '14 #2

h2odev
P: 3
Hi Luuk,
Thanks for reply and your time.

In fact this doesn't resolve my problem. I want to update multiple columns of table `mytable` (children, size) using COUNT() and SUM() on same table `mytable`. Right now I'm using MySQL stored function but i know is possible to update the table without calling functions for each column, but just a JOIN (Equal to virtual/temporary table logic).

Maybe I'm wrong, can you please post a UPDATE query?

Thank You.
Oct 11 '14 #3

Expert 100+
P: 1,031
The query with a jOIN, which 'determines' the new value for childeren is:
Expand|Select|Wrap|Line Numbers
  1. SELECT  mt.*, count(*) CNT  
  2. from mytable mt, mytable mtc 
  3. where mtc.path like concat('%',mt.id,'%') 
  4. group by mt.id;
Converted into an update:
Expand|Select|Wrap|Line Numbers
  1. UPDATE mytable m
  2. LEFT JOIN (
  3.     SELECT  mt.id, count(*) CNT  
  4.     from mytable mt, mytable mtc 
  5.     where mtc.path like concat('%',mt.id,'%') 
  6.     group by mt.id) m2 on m2.id=m.id
  7. SET m.children=ifnull(CNT,0);
Oct 11 '14 #4

h2odev
P: 3
Hi Luuk,

Your query works correctly.

Also I like the idea how you include the WHERE clause and `mytable`.`id` inside subquery, very nice.


I will post here (Just for community wiki) another solution, using User Defined Variables inside User Defined Function to "return" multiple variables.


Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`dbuser`@`localhost` 
  4. FUNCTION `getchildren`( rowid INT, uid INT ) RETURNS INT(11)
  5.  
  6. BEGIN
  7.  
  8. DECLARE children    INT DEFAULT 0;
  9. SET @childrensize := 0;
  10.  
  11.  
  12. SELECT 
  13.     COUNT( `mytable`.`id` ),
  14.     SUM( `mytable`.`privatesize` )
  15. INTO children, @childrensize
  16.  
  17. FROM `dbname`.`mytable`
  18. WHERE 
  19.     `mytable`.`path` LIKE CONCAT( '%/',rowid ,'/%' )
  20.     AND `mytable`.`userid` = uid;
  21.  
  22. RETURN  children;
  23. END


Test New Function:

Expand|Select|Wrap|Line Numbers
  1. SELECT `dbname`.getchildren( 1, 1 ) AS children, @childrensize AS size;
This returns:

Expand|Select|Wrap|Line Numbers
  1. Children   size
  2. ===============
  3. 5          105
  4.  
Now let's update rows with id 2 and 5 (both children and size)

Expand|Select|Wrap|Line Numbers
  1. UPDATE `dbname`.`mytable`
  2. SET 
  3.     `children` = getchildren( `mytable`.`id`, `mytable`.`userid` ),
  4.     `size` = `privatesize` + IFNULL( @childrensize, 0 )
  5. WHERE `mytable`.`id` IN ( 2, 5 )
The logic is simple, each time the function getchildren is called he updates user defined variable @childrensize. If the row has no children the function set @childrensize to NULL, for that IFNULL( @childrensize, 0 ) is necessary.

In this way, the function will called one time for each row and update multiple columns.


Thank You again Luuk.
Oct 11 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.