EXPERT RESPONSE
There are three ways to do this.
select ( select count(*)
from TestTable
where Type = 'A' ) as A
, ( select count(*)
from TestTable
where Type = 'B' ) as B
, ( select count(*)
from TestTable
where Type = 'C' ) as C
This first query does work as is in some databases, but it's
not standard SQL, because of the missing FROM clause.
Add FROM DUAL (or any other 1-row table).
select sum( case when Type = 'A'
then 1 else 0 end ) as A
, sum( case when Type = 'B'
then 1 else 0 end ) as B
, sum( case when Type = 'C'
then 1 else 0 end ) as C
from TestTable
This second query gives you exactly what you asked for, but, like
the first query, will be very bothersome to maintain if you should ever
find yourself needing to add another Type.
select Type
, count(*)
from TestTable
group
by Type
This third query is the best. Not only is it drop-dead simple to understand
at a glance, it also requires no maintenance when new Types
are added. Furthermore, it acknowledges that presentation should not
be handled in the database layer.
By the way, as your table has only one column, which has duplicate
values, this column cannot be the primary key, and so the table
cannot have a primary key. Some people would say it is therefore not
a "real" table, but a
bag.
|
