sql statement

[tirso]

Hi! to all, I want to select in my database those multiple value in a Field and put the result in my datagrid. The problem is I want to display the table like TABLE 1. I have already the code to get the frequency of chiban No but see the result in TABLE 2 followed on my code.

TABLE 1
Source Data (DataBase)
Chiban Frequency
12345
12345
12346
12357
12357

Result (DataGrid View)
Chiban Frequency
12345 2
12357 2

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

TABLE 2.
Chiban Frequency
12345
12345
12357
12357

Thanks in advance

Tirso

 

[lingsn]

I think is something like this

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

 

[tirso]

Thank you so much, It's perfect!

Tirso

 

[tirso]

Hi lingsn,

Another one question, how about if I want to display another field not only chiban like table below

Chiban ID EDABAN FREQUENCY
12345 1 3-1 2


Thanks,
Tirso

 

[lingsn]

Then it is something like this, just put your extra file in the select and also the group:

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