aggregate data as source of datagrid RRS feed

  • Question




    I have table like


    ID   date        count
    a1   05/06/07   20
    a1   05/07/07   10
    a1   05/09/07   2
    a2   05/09/07   25
    a2   05/11/07   22
    a3   05/11/07   20



    and I want to make a new table and display in datagrid counting how many times a unique ID is repeated and what is its count. in this example the, new table/datagrid should look like


    ID     NUM  count
    a1     3       32
    a2     2       47
    a3     1       20


    what is the way to do that, I tried to use the select statement but, I failed

    So, what may be the solution??




    Friday, September 7, 2007 2:21 AM


  • Your datagrid would be read only; except for that, it is easy to do with the select statement and the GROUP BY clause. The only problem I see is the name "count" for the column in your table, which conflicts with the reserved word from SQL, but in SQL Server this is solved by surrounding it with square brackets:


    select ID, count(*) as NUM, sum([count]) from YourTable

    group by ID


    If you are generating the first table, it would be even better to rename the column (and make the SQL statement clearer!)


    - Bruno



    Friday, September 7, 2007 2:58 AM