| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
05-25-10, 10:56
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
|
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?
|
|
06-06-10, 07:51
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Any one got any suggestion or even tips on this? Am still scratching my head to get it working!
|
|
06-07-10, 09:09
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
|
|
actually, this question was already asked and answered a little while back.
Dave
|
|
06-07-10, 13:14
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by dav1mo
actually, this question was already asked and answered a little while back.
Dave
|
Was it? Dont suppose you have the link to it?
|
|
06-07-10, 14:16
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
nope, but start googling recursion or pivot for your answer.
Dave
|
|
06-20-10, 04:30
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by dav1mo
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
|
|
06-21-10, 03:47
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
It is definately possible. Are you saying that you want counts based on the 2nd level of the heirarchy?
|
|
06-21-10, 12:41
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 194
|
|
Quote:
Originally Posted by gvee
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.
|
|
06-22-10, 04:16
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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 
|
|
06-22-10, 08:36
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
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
|
|
06-22-10, 08:38
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
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
|
|
06-22-10, 08:47
|
|
Registered User
|
|
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
...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with Group By Clause
