Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Unanswered: Count occurences

    Dear all,
    I need to find a number of occurrences of certain value in table. The way to find this number is:
    Code:
    select COUNT(val) from my_table GROUP BY "val";
    but my problem is when I want to get this number together with the values of other columns in the row for example
    Code:
    select ID, COUNT(val) from my_table GROUP BY "val";
    I get an error that ID is not included in GROUP BY statement. How to handle this

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krontrex View Post
    How to handle this
    use one query for the counts, and a separate query for the details
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    40
    Could you please write me an example.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2011
    Posts
    42
    please quote with example , sample data.

    It is possible if ID is in a master table and you can refere that ID to the transaction table for a count or sum or average or any other aggregate function.

    It can also work if you have ID and val in the same table with the following SQL

    select ID,count(val) from my_table group by ID

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by krontrex View Post
    Code:
    select COUNT(val) from my_table GROUP BY "val";
    This statement does not make sense to me. What you would get is something like: 2,6,8
    What do you do with that information?

    select ID, COUNT(val) from my_table GROUP BY "val";
    What exactly do you want to count there?
    How many distinct values val has for a certain ID?

    You need to show us some sample data (ideally as INSERT INTO ...) and the desired output based on that sample data.


    Although I do not really understand your question, the following might(!) be what you are looking for:
    Code:
    SELECT id,
          val
          count(*) over (partition by val) as counter
    FROM my_table

Posting Permissions

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