Re: IsNull and aggregate functions

On 14 Feb 2006 02:23:10 -0800, Paul Spratley wrote:

Hi all

Firstly this my first time posting to technical groups - so any
mistakes I apologise for in advance.

I am trying to count records in several secondary tables for the same
run in a primary table. However, there might be no records in these
secondary tables for the specific run. Hence the sql below returns
nulls.

Select run, (select count(errors) from table2 where run = t1.run group
by run) as errors, (select count(user) as users from table3 where run =
t1.run and user = active group by run, dd)
from table1 t1

(Please note the different group bys. )

I do not want nulls to be returned but to be replaced with 0. I have
tried the isnull function but this does not work. eg

Select run, (select isNull(count(errors),0) from table2 where run =
t1.run group by run) as errors, (select isNull(count(user),0) as users
from table3 where run = t1.run and user = active group by run, user)
from table1 t1

Nor will isnull work if I put it around the select clause.

Any suggestions?

Thanks for the help!

Hi Paul,

In the first subquery, I fail to see the reason for adding a GROUP BY
clause. Because of the WHERRE in the subquery, you'll never have more
than one value for table2.run anyway, so the grouping becomes a non-op.
And if no rows match, then a COUNT without GROUP BY will return 0
instead of NULL, so it solves your problem as well.

The same applies to the second subquery. Even though the GROUP BY is
different here, so is the WHERE. (I assume that "active" is a column in
the table1 table, even though you don't qualify it - if both active and
user are in table3, then this query has the risk of resulting in more
than one row, which will cause an error message!)

SELECT run,
(SELECT COUNT(errors)
FROM table2 AS t2
WHERE t2.run = t1.run),
(SELECT COUNT(user)
FROM table3 AS t3
WHERE t3.run = t1.run
AND t3.user = t1.active)
FROM table1 AS t1

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: Optimising the Query
    ... SUM ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Optimising the Query
    ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ... how can I apply concept of matview here? ...
    (comp.databases.oracle.misc)
  • Re: Optimising the Query
    ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ... how can I apply concept of matview here? ...
    (comp.databases.oracle.misc)
  • Delete Query based in a table (different scenario) and fields combination.
    ... Table1 has following fields: ... (PlantCode, Department, Office) ... (Country, PlantCode, Department) ... Now the data on Table3 are Departments ...
    (microsoft.public.access.queries)
  • Re: Other form of CTRL+APOSTROPHE ()
    ... Provided you have a primary key field in the table so that the sorting order is defined, you can execute an Update query that contains a subquery that gets the most recent non-null Part# for each row. ... This example assumes a table named Table1, with an Autonumber named ID to define the sort order: ... Part# Desc QTY UOM ...
    (microsoft.public.access.modulesdaovba)
Loading