How can I use “select distinct” and have a range for my rows? [closed]

Question

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?

Answer 1

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