Posted by Dan Guzman on 02/14/06 14:06

> 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" <paulspratley@yahoo.co.uk> wrote in message
news:1139912589.991885.173810@g14g2000cwa.googlegroups.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!
>

[Back to original message]