How to group values

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hi,

I have a table with the field name CONT_NO. And I have a list of value which
are NIL, HCC302, LDB201, CV/2001/02, etc.

I can now make the following query to get the count of each CONT_NO:

SELECT CONT_NO, COUNT(*) AS ContractCount FROM TABLE GROUP BY CONT_NO;

However, I am now only interested in getting the number of "NIL" and the
number of values that are not "NIL".

How can I do that???

Thanks in advance.

Herbert
 
You might try a query whose SQL looks something like this:

SELECT
(CONT_NO="NIL") AS [NIL],
COUNT(*) AS ContractCount
FROM
[Your Table]
GROUP BY
(CONT_NO="NIL")
 
Try

SELECT Abs(Sum([CONT_NO] = "Nil")) As NilCount,
Abs(Sum([CONT_NO] <> "Nil")) As NonNilCount
FROM YourTable
 
A further question:

Will the following of grouping all the data to only two results faster than
a COUNT(*) for every value?

Herbert

Van T. Dinh said:
Try

SELECT Abs(Sum([CONT_NO] = "Nil")) As NilCount,
Abs(Sum([CONT_NO] <> "Nil")) As NonNilCount
FROM YourTable

--
HTH
Van T. Dinh
MVP (Access)



Herbert Chan said:
Hi,

I have a table with the field name CONT_NO. And I have a list of value which
are NIL, HCC302, LDB201, CV/2001/02, etc.

I can now make the following query to get the count of each CONT_NO:

SELECT CONT_NO, COUNT(*) AS ContractCount FROM TABLE GROUP BY CONT_NO;

However, I am now only interested in getting the number of "NIL" and the
number of values that are not "NIL".

How can I do that???

Thanks in advance.

Herbert
Click to expand...
Click to expand...
 
Not sure but probably not since there are extra arithmetic
and Boolean calculations to group them into 2 groups only.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
A further question:

Will the following of grouping all the data to only two results faster than
a COUNT(*) for every value?

Herbert

"Van T. Dinh" <[email protected]> ¼¶¼g©ó¶l¥ó
Try

SELECT Abs(Sum([CONT_NO] = "Nil")) As NilCount,
Abs(Sum([CONT_NO] <> "Nil")) As NonNilCount
FROM YourTable

--
HTH
Van T. Dinh
MVP (Access)



Herbert Chan said:
Hi,

I have a table with the field name CONT_NO. And I
Click to expand...
have a list of value
which
are NIL, HCC302, LDB201, CV/2001/02, etc.

I can now make the following query to get the count of each CONT_NO:

SELECT CONT_NO, COUNT(*) AS ContractCount FROM TABLE GROUP BY CONT_NO;

However, I am now only interested in getting the number of "NIL" and the
number of values that are not "NIL".

How can I do that???

Thanks in advance.

Herbert
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top