count statement

[tirso]

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).

        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

 

[lingsn]

Try this:

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

 

[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

 

[lingsn]

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

 

[tirso]

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.

        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

 

[lingsn]

So is the above result is what you want?

 

[tirso]

Nop, Result I want is like below

ID KEYCODE CHIBAN FREQUENCY
1 123 12345 2


Thanks again

Tirso

 

[lingsn]

In this case you cannot have that, this is because you have 2 different keycode for same chiban. You will have

ID KEYCODE CHIBAN FREQUENCY
1 123 12345 1
2 125 12345 1

due to different keycode.

 

[tirso]

Ok, now I understand. Thanks a lot

Tirso