Expert SEQUEL

create temp table temp_table(i integer);
 insert into temp_table select count() from data group by entity having count() < 10;
 select count() from temp_table;

This is obnoxious, any way to count the number of rows returned by a select statement without using a temp table?

You didn't even link to the original.  Way to respect your readers.

>>2
post link to original then

>>2,3
``QUEL'' I guess.

Anyway, allow me to rephrase the problem with a better example.

Consider a table with some events, each of which has an associated date. Now, you want to answer the question "how many days have had more than 10 events?"

INSERT INTO temp_table SELECT COUNT() FROM events GROUP BY date;

This creates one row per day in temp_table with the number of events for that given day.

SELECT COUNT() FROM temp_table WHERE i > 10;

And this counts how many of them are higher than 10. The "higher than 10" check can be folded into the first statement in the form of a HAVING clause:

INSERT INTO temp_table SELECT COUNT() FROM events GROUP BY date HAVING COUNT() > 10;

But you must still count manually the number of rows in temp_table:

SELECT COUNT() FROM temp_table;

Some SQL database engines have a non-standard directive to check now may rows are going to be affected by an statement, but I was looking for a more elegant solution.

Use a sub query

SELECT COUNT() FROM (SELECT COUNT() FROM data GROUP BY entity HAVING COUNT() < 10);

Or just:

SELECT COUNT(COUNT()) FROM data GROUP BY entity HAVING COUNT() < 10;

>>6
Oh, so that's the fucking syntax. Thanks. _{I did try to put the subquery inside count(), as well as some other stupid shit.}

The second form seems too good to be true, and indeed it is, at least for sqlite.

>>7
Go here:
http://db.grussell.org/sql/interface.cgi?tn=Tutorial%201&qn=1

paste this in the text box:
select * from department;

select count(count(*))
from employee
group by depno
having count(*) > 5;

then press "Execute Query".

When I don't have access to a database and want to test a query, I usually adapt it and try it on that site. It runs an Oracle database, but I don't know what version.

Actually, paste this in the text box:

select count(*)
from employee
group by depno;

select count(count(*))
from employee
group by depno
having count(*) > 5;

>>8
Okay but I'm not buying Oracle. Goddamn why isn't this fucking language properly standardized. A page to check at once in 3 or 4 DBMs would be awesome.

Heh, Oracle needs some argument to count(). Haven't looked into it deeply, but I got the impression count() should always return the same value for any column name you pass to it (unless you use distinct).

>>10
Haven't looked into it deeply, but I got the impression count() should always return the same value for any column name you pass to it (unless you use distinct).
Yep, that's pretty much it.

I refuse to read lowercase SQL keywords.