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>
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 DOEReceived on Fri May 15 1992 - 22:12:56 CEST