Peter Penzov Peter Penzov - 6 months ago 25
SQL Question

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

Answer
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