Counting zero matches

From: Len Moss <LJM_at_SLACVM.SLAC.STANFORD.EDU>
Date: 15 May 92 20:12:56 GMT
Message-ID: <92136.121256LJM_at_SLACVM.SLAC.STANFORD.EDU>


Can someone help with the following SQL problem?

I have a list of values which I need to order by the number of times each value occurs in a column of a table; that is, I'd like to do something like:

     SELECT Val,COUNT(Val) FROM Table
        WHERE Val IN ('P','Q','R','S',...)
        GROUP BY Val
        ORDER BY 2;

The problem is that some of the values in the list may occur zero times, but I'd like such values to be treated the same as those occurring one or more times. In other words, I want to get back something like the following:

     Val Count(Val)
     --- ----------
     Q            0
     S            1
     R            3
     P           10

I can always query the table once for each value in my list, of course, but it seems to me that SQL ought to be able to produce this result with a single query. Any suggestions? Thanks in advance!

--
Leonard J. Moss <ljm_at_slacvm.slac.stanford.edu> | My views don't necessarily
Stanford Linear Accelerator Center, MS 97      | reflect those of SLAC,
Stanford, CA   94309                           | Stanford or the DOE
Received on Fri May 15 1992 - 22:12:56 CEST

Original text of this message