Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Question Unanswered: Help with Aggregation Query (was "first thread")

    Hello all!
    I have no experience at all, manipulating dbs and therefore I hope to help me with a simple problem that occured today.

    I have imported from excel to access a worksheet as a table.
    The table has a vast number of different data values and appears like that:
    1 aaa toy i
    2 bbb loy
    3 ccc loy
    4 aaa toy o
    5 bbb loy
    6 aaa toy u
    6 aaa toy i

    and I expect results like this
    1 aaa toy i 2
    2 aaa toy o 1
    3 aaa toy u 1
    4 bbb loy 2
    5 ccc loy 1

    In other words I want to count the number of records where certain data values (3) are same
    Last edited by windsurfer; 11-02-06 at 06:29.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select foo, bar, fap, count(*)
      from daTable
    group by foo, bar, fap
    having count(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    11
    where should i place this code;
    Last edited by windsurfer; 11-02-06 at 07:59.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in the SQL View when you design a new query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2006
    Posts
    11
    r937windsurfer

    Thank you very much!

    nevertheless I did a small change

    select foo, bar, fap, count(*)
    from daTable
    group by foo, bar, fap
    having count(*) >= 1

    in order to include these records where the certain values, I was looking for, met only once
    Last edited by windsurfer; 11-02-06 at 09:31.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, if you think about it, you can just simply remove having count(*) >= 1 and get the same results

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •