Very Computer

Hi,
I have the following table:

Field1  Field2  Field3
----------------------
  A       5     aaa
  A       5     bbb
  B       8     ccc
  B       8     ddd
  C       2     eee
  C       2     fff
  C       4     ggg
----------------------
I need a query that would return:

  A       5     aaa   2
  A       5     bbb   2
  B       8     ccc   2
  B       8     ddd   2
  C       2     eee   3
  C       2     fff   3
  C       4     ggg   3

i.e. all rows are selected, plus extra column
added that shows how many rows with certain
value of Field1 are in the table.
Can this be done with SQL, please?
Thanks,
Vadim.

Vadim,

Considering your tablename as test, the query would look like this:

select t.field1, t.field2, t.field3, q.cnt from test t join
(select Field1, count(*) as Cnt from test group by field1) as q
on t.field1 = q.field1

  Hi,
  I have the following table:

  Field1  Field2  Field3
  ----------------------
    A       5     aaa
    A       5     bbb
    B       8     ccc
    B       8     ddd
    C       2     eee
    C       2     fff
    C       4     ggg
  ----------------------
  I need a query that would return:

    A       5     aaa   2
    A       5     bbb   2
    B       8     ccc   2
    B       8     ddd   2
    C       2     eee   3
    C       2     fff   3
    C       4     ggg   3

  i.e. all rows are selected, plus extra column
  added that shows how many rows with certain
  value of Field1 are in the table.
  Can this be done with SQL, please?
  Thanks,
  Vadim.

Stefan,
it works perfect!
Thanks a lot for teaching me a good piece of SQL.
Regards,
Vadim.

Vadim,

Considering your tablename as test, the query would look like this:

select t.field1, t.field2, t.field3, q.cnt from test t join
(select Field1, count(*) as Cnt from test group by field1) as q
on t.field1 = q.field1

    Hi,
    I have the following table:

    Field1  Field2  Field3
    ----------------------
      A       5     aaa
      A       5     bbb
      B       8     ccc
      B       8     ddd
      C       2     eee
      C       2     fff
      C       4     ggg
    ----------------------
    I need a query that would return:

      A       5     aaa   2
      A       5     bbb   2
      B       8     ccc   2
      B       8     ddd   2
      C       2     eee   3
      C       2     fff   3
      C       4     ggg   3

    i.e. all rows are selected, plus extra column
    added that shows how many rows with certain
    value of Field1 are in the table.
    Can this be done with SQL, please?
    Thanks,
    Vadim.