AS/400 >> Count in SQL

by SanderP » Wed, 14 Jun 2006 16:37:12 GMT


I have the following file with customers in it.

Customer code

With an SQL statement I want to retrieve the number of different
customers from the file.
In above example this should be 4.
I was thinking of someting like this:
Select count(*) from (Select CustomerCode from File Group By
But this doesn't seem to work.

Maybe someone can help me with this.

Thanks in advance

AS/400 >> Count in SQL

by 4.spam » Wed, 14 Jun 2006 18:52:59 GMT

select count(distinct CustomerCode) from file;

AS/400 >> Count in SQL

by SanderP » Wed, 14 Jun 2006 19:59:59 GMT


Our customer code exists of two different fields so I use
Select Count(Distinct CustCd Concat CustBr) From File

Thanks again!!

AS/400 >> Count in SQL

by Jonathan Ball » Thu, 15 Jun 2006 03:12:57 GMT

It should, although the count(distinct CustomerCode) is
simpler code. What "doesn't work" in your original

I think I may see the flaw. Your subquery, in
parentheses, is what's called a nested table
expression. You need to give it a table alias, like this:

Select count(*) from
(Select CustomerCode from File Group By
CustomerCode) C

The table alias 'C' could be any valid SQL name: 'X',
'Custs', etc.

Similar Threads

1. SQL another max(count(*))-Question

Dear Sirs and Ladies!

First i am sorry for my bad english.

I have a quite difficult Problem:
I have a table (customers) which has a few fields.
One Field is the ZIP-Code
One Field is the Number of an Employee
Every ZIP-Code belongs to an Employee, but every employee can have more 

Now i wanna know, how much ZIP-Codes every employee has. I used the 
following query:

SELECT col, max(colcount) from
(SELECT col, count(*) as colcount
GROUP BY col) AS aa

This works fine. But i have to spezify TWO columns!!!
i have to get

ZIP-Code, ADM (this is the field which contains the employee), and count
(*) related to ADM per ZIP-Code.

Is there a workaround?

Thank You very much!

LINUX - eigentlich unbezahlbar...

2. Affected SQL Record Count through C Application - AS/400

3. count of records in SQL recordset.

Hi There,

We use a Java swing application on the pc client, talking to an
ile-rpg program on our iseries over a permanent socket. Using this
approach, we can emulate the function of a 5250 green screen page by
page subfile; The user see's what appears to be a huge 100'000 record
table on the screen, however the only data that is transmitted across
the network is the actual 50 or so records that the table is
positioned to. The table positioning is controlled by a scrollbar.

To implement this, we have two types of message that handled by the

BuildList - The server is instructed to build a recordset, and return
the count of records within this set. This count is used to correctly
configure the scrollbars maximum value.

GetlistBlock - the server is instructed to move the cursor to the
appropriate position within the recordset, and send a specific count
of records from that position back to the client.

When we code the BuildList handler, we have to.

A) Select count(*) from {whatever}                   (to get the

B) Declare cursor c1 from {whatever} // open c1.        (to build the

The problem is, the server has to calculate the records that match the
subset (A), and then has to do the same work, and take the same time
in building the cursor (B). In other words, the server has to do the
same database interrogation twice. Now, when the subset is a complex
select statement, that might take say 2 seconds to process the SQL, it
takes 4 seconds to return from our Buildlist function (far too long).
Also, we cann't guarantee that the count = qty of records in recordset
(records may have been added or deleted in the 2 seconds it took to
build the cound).

Does anyone know of anyway we can omit the (A) step. Is there any way
of getting the count of records directly from our declared cursor
rather from a seperate sql statement.

(ps, this is from within SQLRPGLE, I am aware that by using JDBC we
could get a count, but as we are not using any database logic at the
client side, we can not use this method.)



4. Getting multiple count of records that match a certain criteria in one sql query

5. SQL Dynamic Table Count Select Assistance

Can't seem to figure out how to do this and have been reading for some
time now......

I want to select a row count from a table name in SYSTABLES.

This statement does not return what I needed, but can help explain what
I'm looking for. I want the results to be the Row Count from a Table
Name out of the Sub-Select.

Select Count(*) from

Any help would be appreciated.



6. Advanced SQL - Multiple Count statements in one select

7. Counting connections per user using sql


I am using DB2 UDB v 7.2 Fixpak 7.  For a given user I need to find
the number of current connections using sql. Is this possible?
I can find out using "db2 list applications" from the AIX command
line, however I need to know within a client application.

Many thanks,


Nick Lomax

8. SQL count occurrences - AS/400