select id, received_date, count(count_non_web) non_web_count, count(count_web) web_count, (count(count_non_web) / count(*) * 100) non_web_count_percent, (count(count_web) / count(*) * 100) web_count_percent from (select id, to_char(received_date, 'YYYY-mm') received_date, (case when seq_no <= 4000 then 1 else null end) count_non_web, (case_when seq_no > 4000 then 1 else null end) count_web from t where received_date between '01/01/2003' and '12/31/2003') group by id, received_date
HTH, Dave
[EMAIL PROTECTED] wrote:
Dave,
Thanks much! I appreciate your help. One other thing I might have forgotten is there a way to calculate non_web_count - to - total %
and web_count - to total %
so in this way:
output would be like:
ID YEAR NON WEB % WEB %NON-WEB
AC 2003-01 47 98 67.6 32.4
AC 2003-02 26 112
AC 2003-03 57 121
AC 2003-04 40 124
And so forth...
Thanks Dave and evryone else for your help!
Dave Hau <[EMAIL PROTECTED]> wrote:
select id, received_date, count(count_non_web) non_web_count, count(count_web) web_count from (select id, to_char(received_date, 'YYYY-mm') received_date, (case when seq_no <= 4000 then 1 else null end) count_non_web, (case_when seq_no > 4000 then 1 else null end) count_web from t where received_date between '01/01/2003' and '12/31/2003') group by id, received_date
HTH, Dave
[EMAIL PROTECTED] wrote: > Jared, > > Thanks for your fast reply! Sorry if I didn't explain myself clear > enough. Unfortunately count_web is not a column, id||yr||seq_no||ck is a > combination of 4 columns that make up a primary key. if seq_no >4000, in > a id||yr||seq_no||ck row, it's a "web row", if not then it's not. what I > would like to see is: > > ID Received_date No! n-web count Web_count > AR 2003-01 0 4 > AR 2003-02 0 6 > AR 2003-0 1 8 > and so forth..... > > Again thanks for any suggestions you may have! > > Thanks very much! > > > > > > > > [EMAIL PROTECTED] wrote: > > > I'm not sure I entirely understand the result you are trying to > achieve. > > Perhaps this will help > > select received_date, msno, sum(count_web), hardcopy > from ( > select id , > to_char(received_date, 'YYYY-mm') received_date, > id||yr||seq_no||ck MSNO > CASE WHEN seq_no > 4000 then count(seq_no) ELSE 0 end > Count_WEB, > CASE WHEN seq_no <=4000 then count(seq_no) ELSE 0 END > HARDCOPY > from t > where received_date between '01/01/2003' and '12/31/2003' > group by id, > &nbs! p; to_char(received_date, 'YYYY-mm'), > sequence_no, > id||yr||seq_no||ck > ) > group by received_date, msno, hardcopy > / > > > > > Viktor > Sent by: [EMAIL PROTECTED] > > 08/26/2003 01:59 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > > cc: > Subject: SQL HELP!!! > > > > > Hello, > > I was wondering if someone can help me with a report. I am stuck > figuring out what I can do to complete it. Here is what I need to do: > > for a given date range, i..e. start_date - end_date ('01/01/2003', > '12/31/2003'), I need to count all "web", and "non-web" reco! rds. > If seq_no > 4000, then it's web, otherwise non-web. seq_no is not > unique. So it's like 2 different where claused in a single select. > Could I somehow use CASE or ! decode to accomplish this. Here is what > I am trying to do in select: > > select id , > to_char(received_date, 'YYYY-mm'), > id||yr||seq_no||ck MSNO > CASE WHEN seq_no > 4000 then count(seq_no) ELSE 0 end > Count_WEB, > CASE WHEN seq_no <=4000 then count(seq_no) ELSE 0 END > HARDCOPY > from t > where received_date between '01/01/2003' and '12/31/2003' > group by id, > to_char(received_date, 'YYYY-mm'), > sequence_no, > id||yr||seq_no||ck ! > / > > AR 2003-01 AR030023T 0 1 > AR 2003-01 AR0200302 0 1 > AR 2003-01 AR020047K 0 1 > AR 2003-01 AR020077N 0 1 > > I would like to show Year-Month once and count all instances of > id||yr||seq_no||ck (primary_key) for that Year-Month, but not to > break on it, and unfortunaley it won't let me do it without grouping > on seq_no > > Please advise!!! > Any help is g! reatly appreciated! > > > > ------------------------------------------------------------------------ > Do you Yahoo!? > Yahoo! SiteBuilder > - > Free, easy-to-use web site design software > > ------------------------------------------------------------------------ > Do you Yahoo!? > Yahoo! SiteBuilder > - Free, > easy-to-use web site design software
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- Author: Dave Hau
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
------------------------------------------------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder <http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com> - Free, easy-to-use web site design software
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Hau INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
