August 31, 2009 at 12:23 pm
I am stuck on a problem which I originally thought would be quite simple. I'm finding it difficult to explain so it's probably best just to give you the example and see if anyone can assist me with this problem:
I have a table called Current_Roster with a column called Territory_ID. We use logic in our territory ID's so that each sales rep "rolls up" into their district (example, H21H01 and H21H02 all roll up to H21000 and so forth).
What I am trying to do is to Count all of the reps for each of the districts. This is easy to do for any 1 district: SELECT Count(Territory_ID) AS H21000 From Current_Roster Where Territory_ID Like 'H21H%'
I can only do this for 1 district though, I need to be able to count all the rows for all 16 districts.
I thought I would be able to use the Territory_ID Columns Alias to allow me to do this: something like:
SELECT Count(Territory_ID) AS District1, Count(Territory_ID) AS District2 From Current_Roster Where District1 Like 'H21H%' etc..
Now I am just confusing myself!!!
So how do I go about counting different sets of values in the same column and returning the count into it's own column? Do I have to create 16 views to do this? Or do I need a bunch of subselect queries?
Any help would greatly be appreciated!
August 31, 2009 at 12:28 pm
If you use "left(Territory_ID, 4)" in your Select and your Group By, will that do what you need?
- 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
August 31, 2009 at 12:48 pm
Unfortunately that doesn't help. I need to know how many territories are in each district and return each of those values into their own column.
What you are saying is:
SELECT (LEFT(Territory_ID, 4)) FROM Current_Roster
GROUP BY LEFT(Territory_ID, 4)
And that returns
H210
H220
H230
etc....
I could add a Count to that which would look like:
SELECT COUNT(LEFT(Territory_ID, 4)) AS Expr1
FROM dbo.Boss_Roster
GROUP BY LEFT(Territory_ID, 4)
Which returns
6
5
6
8
What I really need is the Count numbers inverted into columns such as:
District | District2 | District 3 | District 4 | etc...
6 | 5 | 6 | 8 | etc...
Thanks
August 31, 2009 at 12:58 pm
perhaps you should give us all of the requirement first. Before we try to solve it.
[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]
August 31, 2009 at 1:12 pm
My apologies, I thought I had done so in my first post but my head is up in the clouds right now trying to figure this out. Let me try and be as specific as possible.
Need:
Create a view that contains the number of territories in each district. Each District should be it's own column and the number of territories in that district should be in a row.
Structure:
1 table called current_roster
1 column (there are others but I am not concerned about them) called Territory_ID
Within the Territory_ID column there are 16 districts, H21000, H22000, H23000, H24000 etc...
The way to determine if a territory belongs to a district is if it begins with H21H00, H22H00, etc...
I can easily count the number of territories in a single district using Count() in a Select Statement. However, I need to return the number of territories in every single district each into their own column within the view.
The problem I am having is that you cannot use Select Aliases in a Where clause so I do not know how to do this.
I know that I could create 16 different views - one for each territory - and then another view that selects each of those 16 to derive at this answer but I'd like to do it the right way....... I just don't know what that is ;Y )
August 31, 2009 at 2:31 pm
I knew I was making a mountain out of a mole hill. I just needed to add select queries within the Select query in order to make each count it's own row.
Here's my final solution, abbreviated to the first three returns:
SELECT COUNT(Territory_ID) AS TotalCount,
(SELECT COUNT(Territory_ID) AS H21000
FROM dbo.Current_Roster
WHERE (Territory_ID LIKE 'H21H%')) AS H21000,
(SELECT COUNT(Territory_ID) AS H22000
FROM dbo.Current_Roster AS Boss_Roster_2
WHERE (Territory_ID LIKE 'H22H%')) AS H22000,
(SELECT COUNT(Territory_ID) AS H23000
FROM dbo.Current_Roster AS Boss_Roster_2
WHERE (Territory_ID LIKE 'H23H%')) AS H23000
FROM dbo.Current_Roster
One of these days things are gonna click with me and it's not going to take me two hours to figure something this simple out.
Thanks for the help
August 31, 2009 at 2:56 pm
That'll work so long as you don't ever add more territories. If adding more territories is even vaguely possible, I'd look into doing a dynamic pivot/cross-tab query, or into doing a more usual query and letting the front-end presentation layer handle the pivoting.
If, for example, you had:
select left(territory_id,4) as Territory, count(*) as Qty
from dbo.CurrentRoster
group by left(territory_id, 4);
You could either use that to generate a pivot table (cross-tab), in SQL, or you could have the front-end pivot it for you.
Personally, I prefer to let the front-end do that, because Excel/Reporting Services/Crystal Reports/etc., are all better at pivoting than SQL is.
- 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
August 31, 2009 at 3:27 pm
Stepping back for a moment and rethinking what I am trying to do I think that your way makes much more sense. Rather than doing a left 4 (which gives me only the territories and will lead to extra rows for the District Managers) I should probably do a left 3 and then in the Count subtract 1 from it (to remove the DM from the count.
Thanks for the suggestion!
September 2, 2009 at 6:50 am
You're welcome.
- 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
September 3, 2009 at 9:45 am
If you really need your results in columns rather than rows, you can either use the PIVOT command or emulate a PIVOT using CASE statements (useful in pre-2005 versions of SQL Server or when you need to do more than one kind of aggregation). Unfortunately, as GSquared pointed out, neither of these solutions is scalable (if you add a territory, they'd have to be updated). And as GSquared also pointed out, you can use his base query and PIVOT the results in the f/e (e.g. in Excel). But in case you want a taste of how to do the pivots in SQL, here are two possibilities (caveat: I didn't have the test tables to try this code before posting, but I ran similar queries against my own table so i think they're okay):
PIVOT method:
SELECT
H21 - 1 AS H21000,
H22 - 1 AS H21000,
H23 - 1 AS H23000
FROM
(
SELECT
LEFT(Territory_id, 3) AS Territory_id
FROM
dbo.Current_Roster
) AS cr
PIVOT
(
COUNT(Territory_id) FOR Territory_id IN ([H21], [H22], [H23])
) AS P
CASE method:
SELECT
SUM
(
CASE WHEN (LEFT(Territory_id, 3) = 'H21') THEN
1
ELSE
0
END
) - 1 AS H21000,
SUM
(
CASE WHEN (LEFT(Territory_id, 3) = 'H22') THEN
1
ELSE
0
END
) - 1 AS H22000,
SUM
(
CASE WHEN (LEFT(Territory_id, 3) = 'H23') THEN
1
ELSE
0
END
) - 1 AS H23000
FROM
dbo.Current_Roster
Edit: Incorrectly used COUNT, not SUm, in my original post.
September 3, 2009 at 11:42 am
I have a table called Current_Roster with a column called Territory_ID. We use logic in our territory ID's so that each sales rep "rolls up" into their district (example, H21H01 and H21H02 all roll up to H21000 and so forth).
I'm late to the party-- but wanted to at least put this info out there (this may come under the heading of "you don't wanna know").
The territory id columns as you describe actually contain 2 pieces of information which iirc is not following the relational model (hey, look at me... I'm channeling J** C****!).
It also looks like you're saying there's a 1:1 relationship between sales rep and territory id... but I may be wrong on that point.
If you're lucky your environment also contains a territory table and a district table. And the territory contains a foreign key pointing at the district it belongs to. If that's true you could build the select to get all the info you need-- but with columns District, CountOfReps.
Then-- as already noted-- you'd have to pivot. And unless you do dynamic sql you need to know how many districts you have.
September 3, 2009 at 12:25 pm
Thanks to all who replied for the good ideas. I like the pivoting examples as those will help me as I continue down my learning curve.
***********************************************
If you're lucky your environment also contains a territory table and a district table. And the territory contains a foreign key pointing at the district it belongs to. If that's true you could build the select to get all the info you need-- but with columns District, CountOfReps.
**********************************************
I wish the above statement were true. It would have saved me lots of headaches when I designed our "Reach & Frequency" reporting cube. I'm seeing lots of "not ideal" design in the current infrastructure and will be looking at enhancements in the future.... when I migrate to SQL Superstar!
Thanks again
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply