SQL: Group by on value1 and "not" value1?

Question

I have a table with something like the following:

OrderNo, OrderType
1, Type1
2, Type1
3, Type2
4, Type3
5, Type4

Doing a "group by" on OrderType is easy but is there a way to do a "group by" to get something like:

OrderCount, OrderType
2, Type1
3, Not Type1

This is DB2 but ideally I am looking for a solution that will work regardless of DB vendor.

score 7 · Answer 1 · edited Oct 15 '12 at 14:26

7

group by case when OrderType = 'type1' then 'type1' else 'not type 1' end

edited Oct 15 '12 at 14:26

lc.

113,939
20
158
187

answered Oct 15 '12 at 14:25

paul

21,653
1
53
54

Charles Bretana · Accepted Answer · 2012-10-16T15:17:45.227

1

try:

Select Case OrderType When 'Type1' Then 'Type1'
            Else 'NotType1' End OrderType, 
       Count(*) OrderCount
From Table
Group By Case OrderType When 'Type1' Then 'Type1'
              Else 'NotType1' End

edited Oct 16 '12 at 15:17

answered Oct 15 '12 at 14:28

Charles Bretana

143,358
22
150
216

Thank you, this is a clear, simple example that works nicely. – Matt Oct 15 '12 at 14:40

SQL: Group by on value1 and "not" value1?

2 Answers2