Qfyilyi

I've a query like this (the original is much more complicated)

select 0, 999, count(case when cond = 'aa' then 1 end) val1, sum(case when cond = 'aa' then amount else 0 end) amount1,

   count(case when cond = 'bb' then 1 end) val2, sum(case when cond = 'bb' then amount else 0 end) amount2

from myTable

where amount between 0 and 999

union

select 1000, 2999, count(case when cond = 'aa' then 1 end) val1, sum(case when cond = 'aa' then amount else 0 end) amount1,

   count(case when cond = 'bb' then 1 end) val2, sum(case when cond = 'bb' then amount else 0 end) amount2

from myTable

where amount between 1000 and 2999

union

...

There are multiple union and the select is way more complicated.

I'd like to simplify it. For this, I used the with clause

with q1 as(

    select case when cond = 'aa' then 1 end val1, case when cond = 'aa' then amount else 0 end amount1,

           case when cond = 'bb' then 1 end val2, case when cond = 'bb' then amount else 0 end amount2

)

select 0, 999, sum(val1) val1, sum(amount1) amount1, sum(val2) val2, sum(amount2) amount2

from q1 where amount between 0 and 999

union

select 1000, 2999, sum(val1) val1, sum(amount1) amount1, sum(val2) val2, sum(amount2) amount2

from q1 where amount between 1000 and 2999

union

....

But in my case this is still very long.
Is there a possibility in plsql to write it like this

with q1 as (

   select minVal, maxVal, count(case when cond = 'aa' then 1 end) val1, sum(case when cond = 'aa' then amount else 0 end) amount1,

      count(case when cond = 'bb' then 1 end) val2, sum(case when cond = 'bb' then amount else 0 end) amount2

   from myTable

   where amount between minVal and maxVal

)

      select * from q1 insideWhere minVal=0 and maxVal=999

union select * from q1 insideWhere minVal=1000 and maxVal=2999

union ...

Or any other solution?

This sounds like it should be a single aggregate query, e.g.:

SELECT min_val,

       max_val,

       COUNT(CASE WHEN cond = 'aa' THEN 1 END) val1,

       SUM(CASE WHEN cond = 'aa' THEN amount ELSE 0 END) amount1,

       COUNT(CASE WHEN cond = 'bb' THEN 1 END) val2,

       SUM(CASE WHEN cond = 'bb' THEN amount ELSE 0 END) amount2,

FROM   (SELECT cond,

               amount,

               CASE WHEN amount BETWEEN 0 AND 999 THEN 0

                    WHEN amount BETWEEN 1000 AND 2999 THEN 1000

               END min_val,

               CASE WHEN amount BETWEEN 0 AND 999 THEN 999

                    WHEN amount BETWEEN 1000 AND 2999 THEN 2999

               END max_val

        FROM   mytable) -- using a subquery here to avoid repeating the case statement in both the select column list and the group by column list

GROUP BY min_val,

         max_val;

To do what you're attempting you need to manifest the min/max groups into a table-like structure. You're already most of the way there: the easy way to do this is to use the WITH clause.

WITH value_groups AS

         (SELECT 0 AS min_val, 999 AS max_val FROM DUAL

          UNION ALL

          SELECT 1000, 2999 FROM DUAL),

     q1 AS

         (SELECT *

          FROM   my_table

                 PIVOT

                     (COUNT (*) FOR cond IN ('aa' AS aa, 'bb' AS bb)))

SELECT   min_val,

         max_val,

         COUNT (aa) as val1,

         SUM (aa * amount) as amount1,

         COUNT (bb) as val2,

         SUM (bb * amount) as amount2 

FROM     q1 JOIN value_groups ON amount BETWEEN min_val AND max_val

GROUP BY min_val, max_val

I've also converted your CASE statements into a pivot. This will group matching sets of count/amount (i.e. if there are two 'aa' rows with an amount of 100, that will be returned as amount=100, count=2), which is why I'm multiplying the two to get the correct sum.