CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1postgresql-9.4
Peter Penzov
Peter Penzov

Reputation: 1658

Count all rows by status

I want to use this table to store tickets in PostgreSQL.

CREATE TABLE TICKET(
 ID INTEGER NOT NULL,
 TITLE TEXT,
 STATUS INTEGER,
 LAST_UPDATED DATE,
 CREATED DATE
)
;

I use column 'status' to set diffrent status(1, 2, 3, 4). How I can count all tickets by status with one SQL query?

I would like to see the final result for example like simple array of numbers:

34, 44, 64, 23

Upvotes: 1

Views: 15687

Answers (4)

PowerStat
PowerStat

Reputation: 3821

select status, count(*) from ticket group by status

Upvotes: 3

ChrisH
ChrisH

Reputation: 31

If you want the values on one row, something like:

SELECT 
  COUNT(CASE WHEN status=1 THEN 1 ELSE NULL END) as '1',
  COUNT(CASE WHEN status=2 THEN 1 ELSE NULL END) as '2',
  COUNT(CASE WHEN status=3 THEN 1 ELSE NULL END) as '3',
  COUNT(CASE WHEN status=4 THEN 1 ELSE NULL END) as '4'
FROM TICKET;

But then you will have to keep your SQL updated with any new status codes.

Upvotes: 2

Sachu
Sachu

Reputation: 7766

Select status, count(*)
from Ticket
group by status

If you have to show status without any tickets also. Then I would follow the below steps. there will be a table to store status details.With out the same we can't know which status is missing in Ticket table

Let say the table is status as below

CREATE TABLE _STATUS(
  STATUS INTEGER,
 STATUS_NAME TEXT
)
;

CREATE TABLE TICKET(
 ID INTEGER NOT NULL,
 TITLE TEXT,
 STATUS INTEGER,
 LAST_UPDATED DATE,
 CREATED DATE
)
;

The query will be

select  s.status,COUNT(t.*)
from _status t left join ticket t
on s.status = t.status
group by s.status

Upvotes: 5

Ely
Ely

Reputation: 11152

select status, count(*) from ticket group by status;

Upvotes: 1

Related Questions