Posted by Hugo Kornelis on 02/14/06 23:30

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

[Back to original message]