count statement

tirso

Active member
Joined
Nov 15, 2006
Messages
31
Programming Experience
1-3
Hi! to all! Does anyone can help me on how display many fields after I use the count(*). I have already a code to check the multiple value of field and calculate the frequency base the count but my problem only one field will display. See the Table 1 for the result of my present code and see Table 2 for the result I wanted. Additional field I want to display is (Edaban and ID).

VB.NET:
        objCommand = New OleDbCommand("SELECT Chiban, count(*) AS Frequency from TableData where chiban" & _
            " In (select chiban from tabledata GROUP BY Chiban HAVING" & _
            " Count(*)>1) GROUP BY Chiban ORDER by chiban", objConnection)

Table 1
Chiban Frequency
12345 2

Table2
Chiban Edaban ID Frequency
12345 23 1 2


Thanks in advance

Tirso
 
Hi Lingsn, but the result is, see Table 1. There is a repitition of rows. What I want is to display like Table 2. It is possible. Thanks for your time.

Table1
Chiban Edaban ID Frequency
12345 23 1 1
12345 23 1 1

Table1
Chiban Edaban ID Frequency
12345 23 1 2
 
In this case, I think the sql should work, and I expecting may be some field that have the empty space, how about trying this out?

SELECT Chiban, TRIM(ID), TRIM(EDABAN) , Count(*) AS Frequency from TableData where Chiban In (select Chiban from TableData GROUP BY Chiban HAVING
Count(*)>1) Group by Chiban, TRIM(ID), TRIM(EDABAN) ORDER by Chiban
 
Hi! Lingsn

Still the same output. The actual contents of my table is Table 1 and the result is see Table 2 for my code. If I use TRIM like TRIM(KEYCODE) the output field is EXPR instead KEYCODE.

VB.NET:
        objCommand = New OleDbCommand("SELECT Chiban, Keycode, count(*) AS Frequency from TableData where chiban" & _
            " In (select chiban from tabledata GROUP BY Chiban HAVING" & _
            " Count(*)>1) GROUP BY Chiban, Keycode ORDER by chiban", objConnection)

Table 1
ID KEYCODE CHIBAN FREQUENCY
1 123 12345
2 125 12345
3 126 12346
4 125 12347

Table 2
ID KEYCODE CHIBAN FREQUENCY
1 123 12345 1
2 125 12345 1
 
Back
Top