CODEWITHSUNDEEP
sqlpostgresql
AndrasCsanyi
AndrasCsanyi

Reputation: 4255

How can I count occasions of grouped values in a table?

I have the table in my postgres db below. I would like to know how many times the the values (name1, name2, name3) occur in the table where trial is 1. In the case below the expected output:

name1, 4
name2, 3
name3, 2

+--------------+
| id|name|trial|
+--------------+
|1  |name1|1   |
|2  |name1|1   |
|3  |name1|1   |
|4  |name1|1   |
|5  |name2|1   |
|6  |name2|1   |
|7  |name2|1   |
|8  |name3|1   |
|9  |name3|1   |

What I tried so far:

SELECT count(C.NAME)
FROM FIRST AS C
WHERE NAME = (
              SELECT CS.NAME
              FROM FIRST AS CS
              WHERE TRIAL = 1
              GROUP BY CS.NAME
              )

this query returns with 9, which is number of rows.

Upvotes: 0

Views: 23

Answers (1)

Felipe Martins
Felipe Martins

Reputation: 184

You're missing the group by clause. Also, the query can be simplified, try this:

SELECT count(1), Name
FROM FIRST
WHERE TRIAL = 1
GROUP BY Name

Upvotes: 2

Related Questions