Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 24, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

COUNT Function: Getting a Series

What is the syntax to get a series of "counts" on a table? An example of what I am trying to do is as follows:
SELECT col1 = COUNT(tstfield1='X'),col2 = COUNT(tstfield2='Y')
     FROM Testfile WHERE name LIKE 'JACK%'
The above is not working. However, the following:
SELECT col1=(SELECT COUNT(tstfield1) FROM Testfile WHERE tstfield1='X'),
     col2=(SELECT COUNT(tstfield2) FROM Testfile WHERE tstfield2='Y')
     FROM Testfile WHERE name LIKE 'JACK%'
works fine. Unfortunately, I can reference only 16 fields (subqueries), but I need to get the counts for approximately 27 fields!

Most products do not allow more than one of the same aggregate function in a single SQL statement. That's why your subqueries work and the one without the subqueries doesn't. The limit on 16 subqueries may seem restrictive, but if you have even that many, you may find the performance unacceptable.

An alternative is to create a temporary table. Insert the result of each of the COUNTs into a row of the table, then query the temporary table.

Thanks to Carl Federl for an additional solution that will work if your database supports these standard functions:

    SELECT SUM(cast(IF tstfield1 = 'X" THEN 1 else 0 endif as int)) as col1,
       SUM(cast(IF tstfield2 = 'X" THEN 1 else 0 endif as int)) as col2,
       SUM(cast(IF tstfield3 = 'X" THEN 1 else 0 endif as int)) as col3
    FROM Testfile
    WHERE name like 'JACK%'
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date