views:

44

answers:

2
SELECT a,b,count(*)
FROM t
GROUP BY rollup(a,b)

result:

a1, b1, 10
a1, b2, 90
a1,   , 100

i need:

a1, b1, 10, 100
a1, b2, 90, 100

how?

+1  A: 

Use:

  SELECT t.a,
         t.b,
         COUNT(*),
         (SELECT COUNT(*)
            FROM YOUR_TABLE
        GROUP BY t.a) 
    FROM YOUR_TABLE t
GROUP BY t.a, t.b
OMG Ponies
This accesses YOUR_TABLE twice, which is not necessary.
Rob van Wijk
@Rob van Wijk: I agree that it accesses the table twice, but there's no alternative if analytics aren't supported (pre 8i)
OMG Ponies
+4  A: 

This table simulates your situation:

SQL> create table t (a,b)
  2  as
  3   select 'a1', 'b1'
  4     from dual
  5  connect by level <= 10
  6    union all
  7   select 'a1', 'b2'
  8     from dual
  9  connect by level <= 90
 10  /

Table created.

Your result with only three rows misses the grand total, so that's a little inaccurate: rollup(a,b) results in 3 grouping sets with 4 rows.

SQL> select a
  2       , b
  3       , count(*)
  4    from t
  5   group by rollup(a,b)
  6  /

A  B    COUNT(*)
-- -- ----------
a1 b1         10
a1 b2         90
a1           100
             100

4 rows selected.

With a regular group by and an analytic function on top, you can achieve your desired result set:

SQL> select a
  2       , b
  3       , count(*)
  4       , sum(count(*)) over (partition by a)
  5    from t
  6   group by a
  7       , b
  8  /

A  B    COUNT(*) SUM(COUNT(*))OVER(PARTITIONBYA)
-- -- ---------- -------------------------------
a1 b2         90                             100
a1 b1         10                             100

2 rows selected.

Regards, Rob.

Rob van Wijk
great! thank you!
mitsky