1. Registered User
Join Date
Jan 2009
Posts
11

I have the following data to display as a table.
The table should display:
location, total no of targets, number of targets in each cost range.
The cost ranges are 3-4.99 and 5-6.99

loc_id target_id cost range
-- ---------- ----------
1 291 5 – 6.99
2 212 5 – 6.99
2 231 5 – 6.99
8 6 5 – 6.99
8 12 3 – 4.99
8 15 5 – 6.99
8 198 5 – 6.99
9 51 5 – 6.99
9 239 5 – 6.99

for example, location 8 has 3 targets in 5-6.99 cost range and 1 target in 3-4.99 cost range.
This should display as follows.

location | total no. of targets | no. of targets in cost range 3-4.99 | no. of targets in cost range 5-6.99
-------- ------------------- ----------------------------------- ----------------------------------
8 4 1 2

how can i do this?

2. Registered User
Join Date
Nov 2003
Posts
2,993
I sure hope the "range" column is just for display and you are not doing any "maths" with that. The range should go into a separate table with a start and end value defining the range and the main table should only reference the entries from the range table.

Now for your problem. Something like the following should do what you want:

Code:
```select distinct loc_id,
count(*) over (partition by loc_id) as "total no. of targets",
case
when cost_range = '3-4.99' then count(*) over (partition by loc_id, cost_range)
else null
end as "no. of targets in cost range 3-4.99",
case
when cost_range = '5-6.99' then count(*) over (partition by loc_id, cost_range)
else null
end as "no. of targets in cost range 5-6.99"
from range
order by loc_id
;```

3. Registered User
Join Date
Jan 2009
Posts
11
thx for ur help. i found the following and post for others who has a similar problem.

sum (case when floor(cost) <1 then 1 else 0 end ) as "<1",
sum (case when floor(cost) >=1 and floor(cost) <= 2 then 1 else 0 end ) as "1 – 2.99",
sum (case when floor(cost) >=3 and floor(cost) <= 4 then 1 else 0 end ) as "3 – 4.99",
sum (case when floor(cost) >=5 and floor(cost) <= 6 then 1 else 0 end) as "5 – 6.99",
sum (case when floor(cost) >=7 then 1 else 0 end ) as ">=7"

4. Registered User
Join Date
Nov 2003
Posts
2,993
thx for ur help. i found the following and post for others who has a similar problem.

sum (case when floor(cost) <1 then 1 else 0 end ) as "<1",
sum (case when floor(cost) >=1 and floor(cost) <= 2 then 1 else 0 end ) as "1 – 2.99",
sum (case when floor(cost) >=3 and floor(cost) <= 4 then 1 else 0 end ) as "3 – 4.99",
sum (case when floor(cost) >=5 and floor(cost) <= 6 then 1 else 0 end) as "5 – 6.99",
sum (case when floor(cost) >=7 then 1 else 0 end ) as ">=7"
Which means that the data in your table is stored in a completely different way compared to the sample data of your initial post.
Please remember to show the "real" sample data in the future!

5. Registered User
Join Date
Jan 2009
Posts
11
the requirement was quite complicated. so i thought of putting down an intermediate output and sought help to get the final output. sorry if i have confused you.

Posting Permissions

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