Show records that count 0

[dafarm]

Here is the code:

SELECT dbo_ALLDSLAMS2.DSLAM, Count(dbo_ALLDSLAMS2.DSLAM) AS CountOfDSLAM, dbo_ALLDSLAMS2.FORMAT, dbo_ALLDSLAMS2.PORTID
FROM dbo_ALLDSLAMS2
GROUP BY dbo_ALLDSLAMS2.DSLAM, dbo_ALLDSLAMS2.FORMAT, dbo_ALLDSLAMS2.PORTID
HAVING (((dbo_ALLDSLAMS2.PORTID)<"0" Or (dbo_ALLDSLAMS2.PORTID) Is Null))
ORDER BY dbo_ALLDSLAMS2.DSLAM;

This does report how many ports are NOT being used, but if all the ports are being used I need it to report 0 or NONE. Is this possible in one query?

Thanks.

 

[Golom]

The logic of the thing is that you are showing the fields
[tt]
DSLAM
FORMAT
PORTID
[/tt]
in addition to the count. If none of them qualify (i.e. they don't match the HAVING clause conditions) then what would you expect to be displayed for those fields?

You might try

SELECT DSLAM, Count(DSLAM) AS CountOfDSLAM, FORMAT, PORTID

FROM dbo_ALLDSLAMS2

WHERE PORTID<"0" Or PORTID Is Null

GROUP BY DSLAM, FORMAT, PORTID

UNION 

Select TOP 1 'NONE', 0, ' ', ' '
FROM dbo_ALLDSLAMS2
WHERE NOT EXISTS (Select PORTID From dbo_ALLDSLAMS2
                  WHERE PortID >= '0')

ORDER BY DSLAM;

 

[PHV]

You may try this:
SELECT DSLAM, Count(DSLAM) AS CountOfDSLAM, FORMAT, PORTID
FROM dbo_ALLDSLAMS2
WHERE PORTID<'0' OR PORTID Is Null
GROUP BY DSLAM, FORMAT, PORTID
UNION SELECT '_Total', Count(*), Null, Null
FROM dbo_ALLDSLAMS2
WHERE PORTID<'0' OR PORTID Is Null
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dafarm]

Neither of those worked. Here is the output:

DSLAM CountOfDSLAM FORMAT PORTID
ALRDDSL 16 REACH
BRDGDSL 10 REACH
CHIMDSL 5 REACH
CHOCDSL 6 REACH
CLTNDSL 2 ADSL
CLTNDSL 8 REACH
CRABDSL 25 REACH
CULPDSL 2 REACH

Here is the output I need to see:

DSLAM CountOfDSLAM FORMAT PORTID
ALRDDSL 16 REACH
BRDGDSL 10 REACH
CHIMDSL 5 REACH
CHOCDSL 0 ADSL <- manually added to demo
CHOCDSL 6 REACH
CLTNDSL 2 ADSL
CLTNDSL 8 REACH
CRABDSL 25 REACH
CULPDSL 2 REACH


Thanks again.

 

[PHV]

A slow way:
SELECT A.DSLAM
, (SELECT Count(*) FROM dbo_ALLDSLAMS2 WHERE DSLAM=A.DSLAM AND FORMAT=A.FORMAT AND Nz(PORTID,'')=Nz(A.PORTID,'')) AS CountOfDSLAM
, A.FORMAT, A.PORTID
FROM dbo_ALLDSLAMS2 A
WHERE A.PORTID<'0' OR A.PORTID Is Null
GROUP BY A.DSLAM, A.FORMAT, A.PORTID
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dafarm]

None of these worked. Any other ideas?