devxlogo

COUNT Function: Getting a Series

COUNT Function: Getting a Series

Question:
What is the syntax to get a series of “counts” on a table? An exampleof 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 only16 fields (subqueries), but I need to get the counts for approximately 27 fields!

Answer:
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 workif 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%’

See also  How AI Art Generators Can Save Time and Money for Startups
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist