CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
St.Antario
St.Antario

Reputation: 27375

How to compute the count by a condition

I have the following table (call it just table):

id       name         value      group_id
PK    varchar(32)    integer     integer

Now, I need to write a query that returns me SUM(value) grouped by group_id and COUNT(name) where name like '%me%'( I don't need to just compute count grouped by group_id, I need to compute the count satisfies by the condition).

But, I need to do that without writing subqueries, and I'm limited to 9.2. I tend to write a custom aggregate function specific for that needs. Would it be a right solution?

Upvotes: 0

Views: 958

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

Use Conditional aggregation

SELECT GROUP_ID, 
       SUM(VALUE) as SUM_VALUE
       SUM(CASE WHEN NAME LIKE '%ME%' THEN 1 ELSE 0 END) as COUNT_VALUE
FROM 
    TABLE
GROUP BY 
    GROUP_ID;

Also see how CASE expression is vey handy https://exploresql.com/2015/08/18/multipurpose-case-expression/

Upvotes: 1

Jakub Kania
Jakub Kania

Reputation: 16477

It should be noted that case can be replaced by casting a bool to int which is shorter (LIKE can be replaced by ~~ but that is both shorter and less readable).

SELECT   group_id 
         ,Sum(value) AS sum_value
         ,Sum((name LIKE '%me%')::int) AS count_value 
FROM     table
GROUP BY group_id;

Upvotes: 2

user330315
user330315

Reputation:

With 9.4 you can use a filtered aggregate:

select group_id, 
       sum(value)
       count(name) filter (where name like '%me%')
from the_table
group by group_id;

For earlier versions, you need to use a CASE statement:

select group_id, 
       sum(value)
       count(case when name like '%me%' then 1 end) 
from the_table
group by group_id;

This works because aggregates will ignore null values and the case statement will return a NULL if the name doesn't match

Upvotes: 3

Related Questions