Help with query

Hello,
I need help with a semi-complicated(not sure) query. Here is a snip-it from
the table I'm querying:
RATE_CENTER OCN NXX BLOCK_ID Block
Count Per OCN
__________________________________________
_________________
ABERNATHY 1163 298 A
10 unique ocn/nxx combo
ABILENE 4822 439 A
8 not unique combo - eight rows
ABILENE 4822 439 7
ABILENE 4822 439 6
ABILENE 4822 439 5
ABILENE 4822 439 4
ABILENE 4822 439 3
ABILENE 4822 439 2
ABILENE 4822 439 1
ABILENE 4822 439 0
ABILENE 4911 899 A
2 not unique combo - two rows
ABILENE 4911 899 9
ABILENE 4911 899 0
ABILENE 4913 437 A
10 unique ocn/nxx combo
ABILENE 4913 204 A
10 unique ocn/nxx combo
ABILENE 4913 202 A
10 unique ocn/nxx combo
I need a query that starts with the rate center column. From there it looks
at OCN. There can be multiple OCN's per rate center. For each OCN of each
rate center, there can be multiple NXX's. For each NXX, the block ID can be
either "A" or a number between 0 and 9. The 'A' only signifies ownership of
the block... so, if the block ID is A and it's a unique ocn/nxx combo, then
the total block count is 10. Otherwise, the Total block count is the sum of
rows that have the same ocn/nxx combo not including the row with the "A"
block
ID. The resulting block count should be put in a new column.
For example... rate center Abilene, ocn 4913, and nxx 204: the block count
is
10, since the ocn/nxx combo is unique. However, for rate center Abilene,
ocn 4822, nxx 439: the block count is 8, since the ocn/nxx combo is not
unique... there are eight rows with that combo for the rows with 0-7 as the
block id.
I have written a script to handle this in another language, but it would be
much faster and eliminate many steps if I could do it all in one shot with a
sql query. I have practically no experience with sql. Is this possible?
Can someone help?
Thanks,
Pete
You should get what you want with this query:
select
RATE_CENTER,
OCN,
NXX,
case when count(BLOCK_ID) = 1 then 10 else count(BLOCK_ID) end as
[Count Per OCN]
from T
group by RATE_CENTER, OCN, NXX
If you want to update the A rows, I think this will work (untested)
update T set
[Count Per OCN] = coalesce(nullif((
select count(BLOCK_ID) from T T2
where T2.RATE_CENTER = T.RATE_CENTER
and T2.OCN = T.OCN
and T2.NXX = T.NXX
),1) 10)
where BLOCK_ID = 'A'
It should help if there is a clustered primary key or clustered index on
(RATE_CENTER, OCN, NXX).
Steve Kass
Drew University
pmox wrote:

>Hello,
>I need help with a semi-complicated(not sure) query. Here is a snip-it from
>the table I'm querying:
> RATE_CENTER OCN NXX BLOCK_ID Block
>Count Per OCN
> __________________________________________
>_________________
> ABERNATHY 1163 298 A
>10 unique ocn/nxx combo
> --
> ABILENE 4822 439 A
>8 not unique combo - eight rows
> ABILENE 4822 439 7
> ABILENE 4822 439 6
> ABILENE 4822 439 5
> ABILENE 4822 439 4
> ABILENE 4822 439 3
> ABILENE 4822 439 2
> ABILENE 4822 439 1
> ABILENE 4822 439 0
> --
> ABILENE 4911 899 A
>2 not unique combo - two rows
> ABILENE 4911 899 9
> ABILENE 4911 899 0
> --
> ABILENE 4913 437 A
>10 unique ocn/nxx combo
> ABILENE 4913 204 A
>10 unique ocn/nxx combo
> ABILENE 4913 202 A
>10 unique ocn/nxx combo
>
>I need a query that starts with the rate center column. From there it looks
>at OCN. There can be multiple OCN's per rate center. For each OCN of each
>rate center, there can be multiple NXX's. For each NXX, the block ID can be
>either "A" or a number between 0 and 9. The 'A' only signifies ownership of
>the block... so, if the block ID is A and it's a unique ocn/nxx combo, then
>the total block count is 10. Otherwise, the Total block count is the sum of
>rows that have the same ocn/nxx combo not including the row with the "A"
>block
>ID. The resulting block count should be put in a new column.
>For example... rate center Abilene, ocn 4913, and nxx 204: the block count
>is
>10, since the ocn/nxx combo is unique. However, for rate center Abilene,
>ocn 4822, nxx 439: the block count is 8, since the ocn/nxx combo is not
>unique... there are eight rows with that combo for the rows with 0-7 as the
>block id.
>I have written a script to handle this in another language, but it would be
>much faster and eliminate many steps if I could do it all in one shot with a
>sql query. I have practically no experience with sql. Is this possible?
>Can someone help?
>Thanks,
>Pete
>
>
>
|||Steve,
Thanks for the input, you got me on the right track. I should have
mentioned that we do not have update privileges nor can we create temp
tables, so I had to find a work around. This is most likely a crude way to
solve this, but it works. I used 2 nested subqueries. I also needed a
simple join with another table to get some extra info. If you see any ways
to make this better let me know.
Thanks,
Pete
-
SELECT SUBQ2.rc_name,
SUBQ2.ocn,
SUBQ2.npa,
SUBQ2.nxx,
SUBQ2.operating_company_name,
SUBQ2.locality_state,
SUBQ2.category,
CASE WHEN temp_count2 = 0 THEN 10 ELSE temp_count2
END AS total_count
FROM (SELECT SUBQ1.rc_name,
SUBQ1.ocn,
SUBQ1.npa,
SUBQ1.nxx,
SUBQ1.operating_company_name,
SUBQ1.locality_state,
SUBQ1.category,
CASE WHEN MAX(SUBQ1.temp_count1) = 10
THEN COUNT(SUBQ1.temp_count1) - 1
ELSE SUM(SUBQ1.temp_count1)
END AS temp_count2
FROM (SELECT rate_center_name_abbreviation AS rc_name,
tb1.operating_company_number AS
ocn,
npa,
nxx,
block_id,
locality_state,
tb2.operating_company_name,
tb2.category,
CASE WHEN block_id = 'A' THEN 10
ELSE 1
END AS temp_count1
FROM lergadm.lerg6 tb1, lergadm.lerg1 tb2
WHERE locality_state = 'TX' AND
tb1.operating_company_number =
tb2.operating_company_number
GROUP BY rate_center_name_abbreviation,
tb1.operating_company_number,
npa, nxx, block_id,
locality_state,
tb2.operating_company_name,
tb2.category
ORDER BY rc_name, ocn, npa, nxx, block_id DESC)
SUBQ1
GROUP BY SUBQ1.rc_name, SUBQ1.ocn, SUBQ1.npa, SUBQ1.nxx,
SUBQ1.locality_state,
SUBQ1.operating_company_name,
SUBQ1.category
ORDER BY SUBQ1.rc_name, SUBQ1.ocn,
SUBQ1.npa, SUBQ1.nxx) SUBQ2
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23%23dhp2bcEHA.3944@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> You should get what you want with this query:
> select
> RATE_CENTER,
> OCN,
> NXX,
> case when count(BLOCK_ID) = 1 then 10 else count(BLOCK_ID) end as
> [Count Per OCN]
> from T
> group by RATE_CENTER, OCN, NXX
> If you want to update the A rows, I think this will work (untested)
> update T set
> [Count Per OCN] = coalesce(nullif((
> select count(BLOCK_ID) from T T2
> where T2.RATE_CENTER = T.RATE_CENTER
> and T2.OCN = T.OCN
> and T2.NXX = T.NXX
> ),1) 10)
> where BLOCK_ID = 'A'
> It should help if there is a clustered primary key or clustered index on
> (RATE_CENTER, OCN, NXX).
> Steve Kass
> Drew University
>
> pmox wrote:
from[vbcol=seagreen]
Block[vbcol=seagreen]
looks[vbcol=seagreen]
each[vbcol=seagreen]
be[vbcol=seagreen]
of[vbcol=seagreen]
then[vbcol=seagreen]
of[vbcol=seagreen]
count[vbcol=seagreen]
the[vbcol=seagreen]
be[vbcol=seagreen]
with a[vbcol=seagreen]