Q

Three ways SQL can count rows by type

I want to get the individual Type field data and their count in SQL.

I have a table called TestTable with one field, Type Varchar(5). The data in the table is:

Type
A
B
A
C
A
B

So there are six records in the table in a single field. Please help me in writing a query to get following result:

A        B         C
3        2         1

In other words I want to get the individual Type field data and their count.

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.

This was first published in August 2007
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close