Re: two total from select

valigula wrote:
> I need to calculate two differetns totals from the following script.
> one the filter per year and another that return everything. At the
> moment i´m looking at the roolup function but .....
> is it better if i do another subquery ...???
>
> thanks
>
> /* Formatted on 2007/05/21 09:08 (Formatter Plus v4.8.7) */
> SELECT DISTINCT tip.tip_desc, tip.tip_clasact, ma.anyobaja
> -- , (select count (*) from ma_con_matriculas where mat_codigo
> is not null ) totalaltas
> -- ,(select count (*) from ma_con_matriculas where to_char
> (mat_fecha_alta , 'YYYY')= '2006' ) totalanyo
> , bajas.r, bajas.d, bajas.p
> FROM tm_con_clases_medios me,
> (SELECT ma.cla_codigo, ma.mat_codigo,
> TO_CHAR (ma.mat_fecha_baja, 'YYYY') AS
> anyobaja
> FROM tm_con_clases_medios me, ma_con_matriculas ma
> WHERE me.cla_codigo = ma.cla_codigo
> AND ma.cla_codigo = me.cla_codigo
> AND ma.mat_fecha_baja IS NOT NULL) ma,
> (SELECT tip_codigo, tip_desc,
> CASE
> WHEN tip_clasact IN
> ('82', '82', '87', '88')
> THEN tip_clasact || ' +500'
> WHEN tip_clasact IN
> ('77', '78', '79','80', '81','86'
> )
> THEN tip_clasact || ' -500'
> WHEN tip_clasact IN ('70', '85')
> THEN tip_clasact || ' kioscos'
> END tip_clasact
> FROM tm_con_tipos_medios) tip,
> (SELECT mat_codigo, TO_CHAR (mat_fecha_baja,
> 'YYYY') a, mov_tipmov,
> COUNT (DECODE (mov_tipmov, '10',
> mov_tipmov)) r,
> COUNT (DECODE (mov_tipmov, '11',
> mov_tipmov)) d,
> COUNT (DECODE (mov_tipmov, '12',
> mov_tipmov)) p
> FROM ma_con_matriculas LEFT OUTER JOIN
> hi_con_movimientos
> ON mat_codigo = mov_matricula
> GROUP BY mat_codigo,
> TO_CHAR (mat_fecha_baja, 'YYYY'),
> mov_tipmov) bajas
> WHERE me.cla_codigo = ma.cla_codigo
> --AND tip.tip_codigo = me.tip_codigo
> AND ma.cla_codigo = me.cla_codigo
> AND bajas.mat_codigo = ma.mat_codigo
> and anyobaja = '2006'
> GROUP BY tip.tip_desc, tip.tip_clasact, bajas.r, bajas.d,
> bajas.p , ma.anyobaja;

Usually.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org