Re: Inline Query ?

Thanks to everyone for their help, I managed to get it working ! thank you all again !.

Regards,
Veronica.

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0308140548.1f1d5c5d_at_posting.google.com>...
> message_4_u2001_at_yahoo.com (Veronica Loria) wrote in message news:<3a03c004.0308140300.b1182d_at_posting.google.com>...
> > Hello All,
> >
> > I wonder if you can help with a bit of fine tuned SQL, consider
> > this query :
> >
> > SELECT
> > to_char(start_date, 'YYYYMM'),
> > count(home_phone_number) "Valide Customers"
> > FROM
> > customer
> > WHERE
> > home_phone_number NOT LIKE '%##%' AND
> > (start_date between add_months(sysdate, -13) AND
> > add_months(sysdate, -1))
> > GROUP BY to_char(start_date, 'YYYYMM')
> > ORDER BY to_char(start_date, 'YYYYMM');
> >
> >
> > This would print out -broken down by months for the last year- all of
> > the records that have 'valid' phone numbers, as the following shows :
> >
> > TO_CHA Valid Customers
> > ------ ---------------------------------
> > 200207 12868
> > 200208 29031
> > 200209 27300
> > 200210 27612
> > 200211 26869
> > 200212 25156
> > 200301 22733
> > 200302 29401
> > 200303 40473
> > 200304 39232
> > 200305 42294
> > 200306 46869
> > 200307 26326
> >
> >
> > However, do you know of a way I can add to this query a 3rd column
> > that would give me the total base records for each month ? so
> > basically the 3rd column would contain the monthly totals of ALL
> > records created (valid and invalid)... any suggestions for a
> > solution are welcome, please write to my e-mail address if you can,
> > thanks in advance.
> >
> > Kind Regards,
> > Veronica
> > Message_4_u2001_at_yahoo.com
>
> Veronica, if the line "home_phone_number NOT LIKE '%##%'" is what
> eliminates bad numbers then add a label, a, to the from clause then
> prefix all existing column references with a.
>
> Next add to the from clause the same you are currently using minus the
> line above and the order by clause clause as label b:
> from customer a,
> (select to_char(......) as yearmonth, count(*) as totalacct
> .......) b
>
> This is an inline view.
> Next make the first line of your where clause the join condition
> between the two tables (one real table and the inline view):
> where to_char(a.start_date,'yyyymm') = b.yearmonth
>
> now add the b.totalacct column to your select list.
>
> You should have the idea from this.
> HTH -- Mark D Powell --
Received on Fri Aug 15 2003 - 05:45:58 CDT