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%’
