IsNull and aggregate functions

[Paul Spratley]

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!

[Guja]

Paul Spratley је напи�ао

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!

hello!
try this:

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

[Dan Guzman]

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

I would expect ISNULL or COALESCE around the scalar subquery to work. Try
the version below. Note that it's a good practice to specify table aliases
in subqueries and prefix columns accordingly in order to avoid ambiguity.
It is unclear what table(s) the 'active' column is in.

In addition to your query, it's a good practice to include DDL (CREATE TABLE
statements) and perhaps sample data (INSERTs) when posting here. Basically,
a (simplified) script that can be run in Query Analyzer. Many individuals
that answer questions here will take the time to develop and unit test a
working solution.

SELECT
t1.run,
ISNULL((SELECT
COUNT(t2.errors)
FROM table2 t2
WHERE t2.run = t1.run
GROUP BY t2.run), 0) as errors,
ISNULL((SELECT
COUNT(t3.user) AS users
FROM table3 t3
WHERE t3.run = t1.run AND t3.user = active
GROUP BY t3.run, t3.dd), 0)
FROM table1 t1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul Spratley" <pa**********@yahoo.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

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!

[Paul Spratley]

Dan
The above works great - thanks for the help!

Paul

[Hugo Kornelis]

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