March 13, 2008 at 12:34 pm
I have 3 tables - User (has CityId, GroupId), City, Group. I am trying to run a query that would return me a count of users for all cities and all groups.
City Group TotalUsers
Miami Marketing 50
Miami Sales 0
Miami Accounting 5
NY Marketing 0
NY Sales 25
NY Accounting 0
PHL Marketing 12
PHL Sales 4
PHL Accounting 75
I tried this:
Select c.Name, g.Name, Count(u.CityId)
From Group g Left outer join
(Select GroupID, CityID from User
where Joindate >= @startdate And JoinDate <= @enddate) u
right outer join City c on c.ID = u.CityID
on g.ID = u.GroupID
Group by c.Name, g.Name
ORDER BY c.Name, g.Name
but I don't get all the rows I need.
Can someone please help?
Thanks.
- SG
March 13, 2008 at 12:57 pm
Try this:
Select c.Name, g.Name, Count(u.CityId)
From User u
Right outer Join Group g ON (g.ID = U.GroupID
And u.Joindate Between @startdate and @enddate)
Right outer Join City c ON (c.ID = u.CityID)
Group by c.Name, g.Name
ORDER BY c.Name, g.Name
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2008 at 1:03 pm
RBarryYoung,
Thanks for the prompt reply. But that doesn't work. It brings back users only for cities and groups that match the date criteria. If there are no users in a specific city or group those rows are not returned.
- sg
March 13, 2008 at 1:09 pm
Are you looking to start from a list of all groups matched up against all cities? If not - where's the list of all groups by city?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 1:10 pm
select city.name as City, group.name as [Group],
isnull(subqty.qty, 0) as Qty
from dbo.City
cross join dbo.Group
left outer join
(select cityid, groupid, count(*) as Qty
from dbo.User
where joindate >= @startdate
and joindate < @enddate
group by cityid, groupid) SubQty
on city.cityid = subqty.cityid
and group.groupid = subqty.groupid
order by city, group
Try that. (You'll need to format it to fit your needs, of course.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2008 at 1:11 pm
That's right.... I am trying to get a count of users for each of the groups in each of the cities. So if there are no users in Marketing group in Miami, that row would still up with a count of 0.
So in this example, since I have 3 cities and 3 groups, there will be 9 rows returned irrespective of how many rows exist in the user table.
March 13, 2008 at 1:21 pm
About the same as Gsquared - either should work, I think
select cityname as City, groupname as [Groupname],
isnull(subqty.qty, 0) as Qty
from (select groupid, group.name groupname, city.name Cityname, cityid
from dbo.Group CROSS JOIN city) cartesianX
left outer join
(select cityid, groupid, count(*) as Qty
from dbo.User
where joindate >= @startdate
and joindate < @enddate
group by cityid, groupid) SubQty
on cartesianX.cityid = subqty.cityid
and cartesianX.groupid = subqty.groupid
order by cityname, groupname
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 1:24 pm
Thanks for the reply gsquared.
The number of rows returned are correct... but somehow the count is messed up. I am trying to figure out why. The total number of users matching the date range is not the same as the total of all users returned by city/group.
Let me first try to understand what does cross join do?
March 13, 2008 at 1:27 pm
Cross JOIN is also what is called a "cartesian product". Meaning - it essentially matches up every row of table X with ever row of table Y.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 1:35 pm
s gupta (3/13/2008)
Thanks for the reply gsquared.The number of rows returned are correct... but somehow the count is messed up. I am trying to figure out why. The total number of users matching the date range is not the same as the total of all users returned by city/group.
Let me first try to understand what does cross join do?
Try running the sub-query (the derived table) without the outer query, and see if the data from it makes sense.
I also realized that, in my query, I used "<" where you had "<=". That might make a difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2008 at 1:38 pm
Thanks for the reply "Hall of Fame".
This one doesn't seem to work as well (the same as gsquared's). The number of rows returned are fine but totals don't match up.
March 13, 2008 at 1:41 pm
So that's the same as "Full join" right?
March 13, 2008 at 1:41 pm
(As a side note, "Hall of Fame" and other such titles are ranks on this page, the name of the person writing the post is above that, in the pale blue part.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2008 at 1:42 pm
The sub-query returns the right number of rows. I had fixed the where clause when I tried your suggestion to use >= & <=.
March 13, 2008 at 1:42 pm
Name's Matt by the way.
What are you trying to match this up to?
The statement earlier (The total number of users matching the date range is not the same as the total of all users returned by city/group.) would sound like an "of course they wouldn't" to me. After all - if the date filter didn't remove some users from the count, then why would you use it?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply