0

I have the following table columns: ID (id), Type (string. could be either: open or close)

I would like to get an output count where for each unique ID, I count how many times the ID got the open type and how many times it got the close type.

So the output would look like this:

ID | openCount | closeCount
10 | 23        | 2

Any ideas?

strangeQuirks
  • 4,761
  • 9
  • 40
  • 67

2 Answers2

1

select ID,count( * ) AS "openCount" where type = "open" group by ID union select ID,count( * ) AS "closeCount" where type = "close" group by ID;

that should do it. it's count(star) stack overflow doesn't seem to like that :)

Null
  • 61
  • 5
1
SELECT id, SUM(type='open') openCount, SUM(type='closed') closedCount FROM table GROUP BY id;

What happens is that the SUM() function aggregates all type='open' and type='closed' values for all rows which share the same id value, which if true is 1 and if false is 0.

ksiimson
  • 593
  • 3
  • 8