help in sql count

Question

Suppose I have a table with 2 columns (status and date) like the following:

status: U  T  U  U  L       
date:   12 14 15 16 17

Can I (using only 1 SQL statement) count the number of distinct values in the status? That is:

• count(U)=3
• count(T)=1
• count(L)=2
• count(P)=0

Can I do this with 1 SQL query?

Note: I have static values in status. I can only have (U-T-L-P)

Answer 1

You need to use Group By:

SELECT Status, Count(Status)
FROM table
GROUP BY Status

This will not return P = 0 if P is not populated in the table. In your application logic you will need to check and if a certain status is not returned, it means there are no entries (i.e. 0).

SQL cannot query records that are not there.

Answer 2

You can do this with a query which groups on your status column, e.g.

SELECT COUNT(*) as StatusCount, Status
FROM MyTable
GROUP BY Status

Answer 3

This will return a row for every status and the count in the second column:

SELECT Status, COUNT(*) Cnt
FROM Tbl
GROUP BY Status

So it would return

Status  Cnt
 U       3
 T       1
 L       1

for your example (in no defined order). Use ORDER BY if you want to sort the results.

Answer 4

To get the zero for the status P, you have to do some devious stuff using a table that lists all the possible statuses.

SELECT COUNT(A.Status), B.Status
  FROM AnonymousTable AS A RIGHT OUTER JOIN
       (SELECT 'P' AS Status FROM Dual
        UNION
        SELECT 'U' AS Status FROM Dual
        UNION
        SELECT 'L' AS Status FROM Dual
        UNION
        SELECT 'T' AS Status FROM Dual
       ) AS B ON A.Status = B.Status
 GROUP BY B.Status;

The 4-way UNION is one way of generating a list of values; your DBMS may provide more compact alternatives. I'm assuming that the table Dual contains just one row (as found in Oracle).

The COUNT(A.Status) counts the number of non-null values in A.Status. The RIGHT OUTER JOIN lists the row from B with Status = 'P' and joins it with a single NULL for the A.Status, which the COUNT(A.Status) therefore counts as zero. If you used COUNT(*), you'd get a 1 for the count.