Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Help with Group By Clause

    Hi,

    am using the following SQL to see how many user I have from a particular location.

    Code:
    SELECT  l.location_id
    	  , l.location_name
    	  , l.location_parent_id
    	  , l.location_root_id
    	  , COUNT(u.user_id) AS num_users  
    FROM tbl_locations as l
    LEFT JOIN tbl_users AS u ON l.location_id = u.location_id 
    GROUP BY l.location_id
    ORDER BY l.location_id ASC;

    For which i get the following results:

    Code:
    location_id  location_name  	location_parent_id  location_root_id  num_users
    -------------------------------------------------------------------------------
    1		UK			0		0		0
    2 		East Anglia 		1 		1 		0
    3 		Cambridgeshire 		2 		1 		0
    4 		Norfolk 		2 		1 		0
    5 		Suffolk 		2 		1 		0
    6 		East Midlands 		1 		1 		0
    7 		Derbyshire 		6 		1 		0
    8 		Leicestershire 		6 		1 		0
    9 		Lincolnshire 		6 		1 		0
    10 		Northamptonshire 	6 		1 		0
    11 		Nottinghamshire 	6 		1 		0
    12 		Greater London 		1 		1 		0
    13 		Central London 		12 		1 		50
    14 		East London 		12 		1 		5
    Since Central London and East London is part of Greater London how do I group the query so that It also shows 55 users for Greater London?

  2. #2
    Join Date
    Mar 2007
    Posts
    212
    Any one got any suggestion or even tips on this? Am still scratching my head to get it working!

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    actually, this question was already asked and answered a little while back.
    Dave

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by dav1mo View Post
    actually, this question was already asked and answered a little while back.
    Dave
    Was it? Dont suppose you have the link to it?

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    nope, but start googling recursion or pivot for your answer.
    Dave

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by dav1mo View Post
    nope, but start googling recursion or pivot for your answer.
    Dave

    I give up on this. I've spent hours on it - I dont think its possible

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It is definately possible. Are you saying that you want counts based on the 2nd level of the heirarchy?
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by gvee View Post
    It is definately possible. Are you saying that you want counts based on the 2nd level of the heirarchy?
    I need counts based on both the first and second level of the heirachy.

    so if the user registered their location as Greater London instead of narowing it down to a specific area of London i.e. Central London.

    the table would end up as follows:

    Code:
    location_id  location_name  	location_parent_id  location_root_id  num_users
    -------------------------------------------------------------------------------
    12 		Greater London 		1 		1 		1
    13 		Central London 		12 		1 		50
    14 		East London 		12 		1 		5
    In the above instance I want the query to produce num_users for Greater London as 56.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rats, just realised that this is MySQL, not SQL Server! I can do it in SQL Server but am not familiar with any recursion methods in MySQL, sorry
    George
    Home | Blog

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    For just 2 levels and leaving out your master parent you could attempt something along the lines of:
    Code:
    select a.location_id,  a.location_name, a.location_parent_id
            ,a.location_root_id, value(sum(b.num_users),0) + a.num_users
        from my_table a
    left outer join my_table b
       on a.location_id = b.location_parent_id
      and b.location_parent_id <> 1
    I left off the main parent id of 1 as this query would not return the correct results for it as we are only getting the hierachy of 2 levels. I used a LEFT OUTER as a location id may not have a parent other than parent 1. This code is completely untested, but give it a go.
    Dave

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, I wanted to mention that you could try keeping this straight in the future by putting a trigger on the table to increment a counter on the parents' row. This way you could keep all of the parents in line with their children, to include you master parent id of 1(UK).
    Dave

  12. #12
    Join Date
    Jun 2010
    Posts
    4
    You can't achieve it in single query, need to write either stored procedure in db side or iteration in programming language.

    The query below will help to get total, one level up from last child,

    "select l1.location_name,if(u1.num_users IS NULL,0,u1.num_users)+(select if(sum(u2.num_users) IS NULL,0,sum(u2.num_users)) from tbl_locations l2 left join tbl_users u2 on (l2.location_id = u2.location_id) where l2.location_parent_id = l1.location_id) as num_users from tbl_locations l1 left join tbl_users u1 on (l1.location_id = u1.location_id);"

    ...
    Greater London 55
    Central London 50
    East London 5
    ...

Posting Permissions

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