Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wrote this code in PL/SQL developer and was wondering how I could have one row per range of t.ttt in the select distinct columns.

Here's the SQL:

select count (t.aaa), sum (t.bbb)
from xxx t
where t.qqq IN  ('3')
and t.www like 'A'
and t.eee >= TO_DATE('2008/03/20','YYYY/MM/DD')
and t.eee <= TO_DATE('2009/03/21','YYYY/MM/DD')
and t.ttt >= 10 and t.ttt <= 20

What I need is:

  1. I need the count(t.aaa) and sum (t.bbb) as my columns for each t.www in (A,B,C,D). I mean that there must be 2 columns (count and sum) for A, two columns for B, etc. Every counter has 2 columns.
  2. and for the rows, I need t.ttt ranges: [10-20], [21-30], [31-40].

How could I go about doing this?

share|improve this question
    
this has been cross-posted on StackOverflow -- stackoverflow.com/questions/13800286/… –  bluefeet Dec 11 '12 at 11:21
    
I'm closing this because you asked the same Q first on Stack Overflow, as bluefeet pointed out. This is also a borderline basic query syntax question that would be Off Topic for this site regardless of cross posting. –  JNK Dec 11 '12 at 13:11
    
There is no need for "regardless of cross posting". i had a question. so its my right to ask it from the ppl who can help me. u were new in this someday and i am now. and i wont be new for always. its basic for u and average for me dude. –  arash beterkun Dec 12 '12 at 6:53
add comment

closed as off topic by JNK Dec 11 '12 at 13:10

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

Here's an idea you could build upon to match your specific requirements.

To build the different columns, and the ttt ranges, build a query of this form:

select
  case when www = 'A' then aaa end c1_a
, case when www = 'A' then bbb end c2_a
....
, case when ttt >= 10 and ttt <= 20 then 1
       when ttt >= 21 and ttt <= 30 then 2
       when ttt >= 31 and ttt <= 40 then 3 end slice
from foo
where
    ttt >= 10 and ttt <= 40
and ...

The idea is to get the right column value, or null, for each www in its own column, and a distinct value for each range.

Once you have that, aggregating is straightforward:

select
  count(c1_a) cnt_a
, sum(c2_a)   sum_a
...
, slice
from (
  select
      case when www = 'A' then aaa end c1_a
    , case when www = 'A' then bbb end c2_a
    ...
    , case when ttt >= 10 and ttt <= 20 then 1
           when ttt >= 21 and ttt <= 30 then 2
           when ttt >= 31 and ttt <= 40 then 3 end slice
  from foo
  where
      ttt >= 10 and ttt <= 40
  and ...
)
group by slice
order by slice;

Example, assuming:

create table foo (www char(1), ttt number, aaa number, bbb number);
insert into foo values ('A', 10, 1, 1);
insert into foo values ('A', 25, 1, 2);
insert into foo values ('A', 26, 1, 2);
insert into foo values ('A', 27, 1, 2);
insert into foo values ('A', 33, 1, 3);
commit;
insert into foo values ('B', 10, 1, 3);
insert into foo values ('B', 10, 1, 4);
insert into foo values ('B', 25, null, 2);
insert into foo values ('B', 33, 1, 10);
insert into foo values ('B', 37, 1, 11);
commit;

Then:

select
  case when www = 'A' then aaa end c1_a
, case when www = 'A' then bbb end c2_a
, case when www = 'B' then aaa end c1_b
, case when www = 'B' then bbb end c2_b
, case when ttt >= 10 and ttt <= 20 then 1
       when ttt >= 21 and ttt <= 30 then 2
       when ttt >= 31 and ttt <= 40 then 3 end slice
from foo
where ttt >= 10 and ttt <= 40;

Will yield:

      C1_A       C2_A       C1_B       C2_B      SLICE
---------- ---------- ---------- ---------- ----------
         1          1                                1
         1          2                                2
         1          2                                2
         1          2                                2
         1          3                                3
                               1          3          1
                               1          4          1
                                          2          2
                               1         10          3
                               1         11          3

So the aggregation:

select
  count(c1_a) cnt_a
, sum(c2_a)   sum_a
, count(c1_b) cnt_b
, sum(c2_b)   sum_b
, slice
from (
  select
      case when www = 'A' then aaa end c1_a
    , case when www = 'A' then bbb end c2_a
    , case when www = 'B' then aaa end c1_b
    , case when www = 'B' then bbb end c2_b
    , case when ttt >= 10 and ttt <= 20 then 1
           when ttt >= 21 and ttt <= 30 then 2
           when ttt >= 31 and ttt <= 40 then 3 end slice
  from foo
  where ttt >= 10 and ttt <= 40
)
group by slice
order by slice;

Gives:

     CNT_A      SUM_A      CNT_B      SUM_B      SLICE
---------- ---------- ---------- ---------- ----------
         1          1          2          7          1
         3          6          0          2          2
         1          3          2         21          3

Note: if you want the actual number of lines in the count columns, rather than the number of lines with a non-null aaa, replace that part of those cases with something like:

case when www = 'A' then 1 end
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.