Re: SQL Aggregates

I believe that using the nvl() function would help but as for the problem of showing each day of the month will require a join to a table that has each day of the month, otherwise the where clause automatically, disallows days where the value <> 'abc' and therefore will not have a day to group on. For instance assume tab1 has field1 with values '08/01/2000' through '08/31/2000',

then

create view v1
as
select to_char(when,'mm/dd/yyyy') w1,value from T
where value = 'abc';

then:

select field1, count(value)
from tab1,v1
where field1 = w1 (+)
group by field1;

This select will return the values you're interested in.

Nevin Hahn
nhahn_at_evoke.com

Wouter Verhelst wrote:

> In article <8opfuu$jnp_at_dispatch.concentric.net>,
> "Mike DiChiappari" <mike_at_mysticsoft.com> writes:
> > I am having trouble counting rows in a table. I have a table, T, with two
> > columns: when and value. When is a timestamp and value is a varchar(10). I
> > would like to create a report that shows values for a month. I can not
> > create an SQL statement to show every day in the month and a corresponding
> > count. The problem is that days without a particular value don't appear.
> >
> > Here is my query:
> >
> > SELECT TO_CHAR( when, 'MM/DD/YYYY'), COUNT(*)
> > FROM T
> > WHERE value = 'abc'
> > GROUP BY TO_CHAR( when, 'MM/DD/YYYY')
> >
> > The results are fine except that days that don't have a single value with
> > 'abc' are missing. I would like all days to show with a 0 value if 'abc' is
> > missing.
> >
> > Any ideas about how I can do this?
>
> You need to use null value substitution.
> I think this function is called nvs(), but I am not certain.
>
> --
> 2:11am up 3:06, 2 users, load average: 0.02, 0.04, 0.07
>
> Voor een vertaling van Documentation/Configure.help naar het Nederlands:
> http://users.pandora.be/wouter.verhelst/configure.html
>
> It were not best that we should all think alike; it is difference of opinion
> that makes horse-races.
> -- Mark Twain, "Pudd'nhead Wilson's Calendar"
Received on Sat Sep 02 2000 - 00:45:52 CDT