CODEWITHSUNDEEP
sqlgroup-by
Janek
Janek

Reputation: 1491

How to group rows into two groups in sql?

Lets say I have such a table:

id|time|operation
1  2      read
2  5      write
3  3      read
4  7      read
5  2      save
6  1      open

and now I would like to do two things:

  1. Divide all these records into two groups: 1) all rows where operation equals to "read" 2) all other rows.
  2. Sum the time in each group.

So that my query would result only into two rows.

What I got so far is:

select 
 sum(time) as total_time,
 operation
group by
 operation
;

Although that gives me many groups, depending on the number of distinct operations.

How I could group them only into two categories?

Cheers!

Upvotes: 1

Views: 3601

Answers (3)

Kaf
Kaf

Reputation: 33809

Try 

SELECT T.op, sum(T.time)
FROM ( SELECT time, CASE operation 
                WHEN 'read' THEN 'read' ELSE 'not read' END AS op ) T
GROUP BY T.op

Upvotes: 0

user554546
user554546

Reputation:

Alternatively, you can also use a case statement:

select
case when operation != 'read' then 'other'
  else operation end as operation
,sum(time) as total_time
from table
group by case when operation != 'read' then 'other'
  else operation end;

Upvotes: 0

Marc B
Marc B

Reputation: 360662

group by can take arbitrary clauses, so

GROUP BY (operation = 'read')

will work. Essentially you'd be grouping on the boolean result of the comparison, not the value of the operation field, so any record which is "read" will be group 1, and any non-read will be group 0.

Upvotes: 5

Related Questions