Re: Inline Query ?

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 Thu Aug 14 2003 - 08:48:43 CDT