Re: SQL Aggregates

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"