Count occurences

krontrex · #1 (**permalink**) 09-19-11, 11:16

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

r937 · #2 (**permalink**) 09-19-11, 12:39

Quote:

Originally Posted by krontrex

How to handle this

use one query for the counts, and a separate query for the details

krontrex · #3 (**permalink**) 09-19-11, 12:41

Could you please write me an example.

r937 · #4 (**permalink**) 09-19-11, 14:02

which values?

arvindps · #5 (**permalink**) 09-24-11, 05:52

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

shammat · #6 (**permalink**) 09-24-11, 07:21

Quote:

Originally Posted by krontrex

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?

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech