Luke Henz Luke Henz - 1 year ago 106
SQL Question

SQLselect count with case/when

I have the following table:

Id Type
1 A
2 B
3 C
4 A

I would like to create new table that counts the number of rows which each type has. It is easy to create each table for each type counting, but I would like to make it better in looks and also the performance, so is it possible to do this in one query? I have come up with a query like below but it does not work. The error said that "Result of WHEN clause 2 is not the same data type as the preceding result".
Help is appreciated and thanks in advance.

COUNT(Id) as total,
COUNT(CASE WHEN Type = "A" then Id else . end) as typeA,
COUNT(CASE WHEN Type = "B" then Id else . end) as typeB,
COUNT(CASE WHEN Type = "C" then Id else . end) as typeC,
COUNT(CASE WHEN Type <> "A" then Id else . end) as nonTypeA
FROM WORK.ListTable;

Joe Joe
Answer Source

SAS solution is not to use PROC SQL for such a thing. SQL is always going to be a bit slower or harder/messier to code with exceptions (without the NotTypeA this would be easier). In SAS, PROC TABULATE and PROC FORMAT will give you that easily with a quick transpose afterwards. A bit more code but a lot more flexible.

data have;
input Id Type  $;
1  A
2  B
3  C
4  A

proc format lib=work;
value $typeF (multilabel notsorted)

proc tabulate data=have out=want;
format type $typef.;
class type/mlf preloadfmt order=data;
tables type*n;

proc transpose data=want out=want_t(drop=_NAME_);
var N;
id type;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download