Year Function problem (QUERY)

[2122002]

Hi All,

I have a table called DUMMY with the following fields and record:

DONDATE | USAGE | PACKETNO
--------------------------------
03/08/2005 | Yes | AB009
08/06/2004 | Yes | AA112
07/05/2005 | | HJ112

What I want my query to do is to:

Retrieve the year (possible value 2004, 2005), a count of the number of the year, the number (count) where usage is YES, and counts where usage is NULL.

Please help me out.

 

[djbjr]

Try this

Select c_year, sum(yes_count), sum(null_count)
from
(select
to_char(to_date(dondate, 'MM/DD/YYYY'), 'YYYY') c_year,
case when usage = 'YES' then 1 else 0 end yes_count,
case when usage is NULL then 1 else 0 end null count)
group by c_year


Let me know if this is what you need.

 

[Dagon]

select to_number(to_charr(dondate, 'YYYY'),
count(usage),
count(case when usage = 'YES' then 1 else null end),
count(case when usage is null then 1 else null end)
from dummy
group by to_number(to_charr(dondate, 'YYYY')

 

[djbjr]

oops I forgot to add the total count for the year
and I made a syntax error



Select c_year, sum(year_count),
sum(yes_count), sum(null_count)
from
(select
to_char(to_date(dondate, 'MM/DD/YYYY'), 'YYYY') c_year,
1 year_count,
case when usage = 'YES' then 1 else 0 end yes_count,
case when usage is NULL then 1 else 0 end null count
from dummy)
group by c_year

 

[Turkbear]

Hi,
Have you tried:

Select To_char(DONDATE,'YYYY'),to_char(DONDATE,'YYYY') YR,USAGE, Count(*)
from DUMMY
Group by To_char(DONDATE,'YYYY'),to_char(DONDATE,'YYYY'),USAGE

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Base Three,

There are many ways to solve your need...Here is one way. (Since you didn't mention what datatype your "dondate" is, I coded for worst-case scenario...a varchar column.):

select cnt_2004, cnt_2005, cnt_yes, cnt_null
from (select count(*) cnt_2004
        from dummy
       where to_char(to_date(dondate,'mm/dd/yyyy'),'YYYY') = '2004')
    ,(select count(*) cnt_2005
        from dummy
       where to_char(to_date(dondate,'mm/dd/yyyy'),'YYYY') = '2005')
    ,(select count(*) cnt_yes
        from dummy
       where upper(usg) = 'YES')
    ,(select count(*) cnt_null
        from dummy
       where usg is null);

  CNT_2004   CNT_2005    CNT_YES   CNT_NULL
---------- ---------- ---------- ----------
         1          2          2          1

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

Hello all:

djbjr:

Your query returns this error " FROM keyword found where not expected"

Dagon:

Query return error "Missign right parenthesis".

What shoul I DO???

 

[2122002]

Hi SantaMufasa:

I used this date format (for example):

TO_Date( '02/01/2005 4:15:00 PM', 'MM/DD/YYYY HH:MI:SS AM')

However, this is what I have as error to your query:

ERROR at line 7:
ORA-01858: a non-numeric character was found where a numeric was expected


---------------------------
Actually, Im using Oracle 8i, hope this is not the problem....

 

[2122002]

Turkbear:

Your query works.

SantaMusafa, djbjr and Dagon I need your modfication to your query as I am interested to know many syntax to Oracle Query. Therefore, I look forwaord to your query modification.

regards.

 

[Dagon]

A few mistypings in mine:

select to_number(to_char(dondate, 'YYYY')),
count(usage),
count(case when usage = 'YES' then 1 else null end),
count(case when usage is null then 1 else null end)
from dummy
group by to_number(to_char(dondate, 'YYYY'))

Actually, the query can be simplified a bit:

select to_number(to_char(dondate, 'YYYY')),
count(*),
count(case when usage = 'YES' then 1 else null end),
count(usage)
from dummy
group by to_number(to_char(dondate, 'YYYY'))

count(*) gives all rows, count(usage) gives rows where usage is not null.

 

[djbjr]

I left out the underscore on null_count when I applied the alias. This should work now.

Select c_year, sum(year_count),
sum(yes_count), sum(null_count)
from
(select
to_char(to_date(dondate, 'MM/DD/YYYY'), 'YYYY') c_year,
1 year_count,
case when usage = 'YES' then 1 else 0 end yes_count,
case when usage is NULL then 1 else 0 end null_count
from dummy)
group by c_year

 

[SantaMufasa]

Base Three ("2122002"...Get it? <grin>) and the rest of the responders,

Here are the issues that I see with the responses:

1) The various solutions result from Base Three's unclear specifications of exactly how s/he wants the output to appear...Does s/he want one line of output or many?

2) "USAGE" is an Oracle reserved word with special meaning under special circumstances. That is why I changed the spelling to "USG".

3) Turkbear's output appears this way:
[tt]
TO_C YR USG COUNT(*)
---- ---- --- ----------
2004 2004 Yes 1
2005 2005 1
2005 2005 Yes 1[/tt]

Because of the unclear output-design specifications, I cannot determine if this is how Base Three wants it to look.

4) Dagon's code has some syntax issues (besides "USAGE"):

a) "to_charr" should be "to_char",
b) TO_NUMBER function is missing a closing paren, in both occurrences.

Once the syntax issues are resolved (and we add some SQL*Plus "COL" definitions to aesthetically clean up the output appearance), the output is:
[tt]
Year Usage Yes Usage Null Usage
---------- ---------- ---------- ----------
2004 1 0 0
2005 1 0 1
[/tt]
...which are incorrect results...2005's Usage should be "2", not "1".

DJBjr's code has a syntax issue:

a) "null count" should read "null_count"

Once the syntax issue is resolved, here is DJBjr's code output:
[tt]
C_YE SUM(YEAR_COUNT) SUM(YES_COUNT) SUM(NULL_COUNT)
---- --------------- -------------- ---------------
2004 1 0 0
2005 2 0 1
[/tt]

SantaMusafa, djbjr and Dagon I need your modfication to your query as I am interested to know many syntax to Oracle Query.

What modification do you need to my query?

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.