Get count of distinct groups in a single SQL query in Firebird 1.5?

Question

I ran across the following in a stored procedure:

  for
    select 1
    from   scan_queue
    where  ( date_time_locked is null       )
      and  ( scan_seqno >= :varMinScanSeqno )
      and  ( scan_seqno <= :varMaxScanSeqno )
    group by loan_id, collateral_id, insurance_id
    into   varNotUsed
  do
    varItemsToScan = varItemsToScan + 1;

My first thought was that this is probably an inefficient way of counting the number of groups, but my second thought was, "hey, how would you write that in a single query, anyway?" And I didn't have a good answer. (So this is more of an academic question.) I am not looking for a solution that concatenates the IDs, like so:

select count(distinct loan_id || collateral_id || insurance_id)
from   scan_queue
where  ( date_time_locked is null       )
  and  ( scan_seqno >= :varMinScanSeqno )
  and  ( scan_seqno <= :varMaxScanSeqno )

What is the best way to query this information?

EDIT: Since I apparently did not make this clear enough, I am using Firebird v1.5.

Answer 1

What's wrong with plain old count(distinct ..) ?

select count(*) from ( 
select loan_id, collateral_id, insurance_id
from   scan_queue
where  ( date_time_locked is null       )
  and  ( scan_seqno >= :varMinScanSeqno )
  and  ( scan_seqno <= :varMaxScanSeqno )
group by loan_id, collateral_id, insurance_id
)

Answer 2

don't know if it's more or less efficient, but a subquery would do it.

select count(grouping) 
from
( select count(*) as grouping
    from   scan_queue
    where  ( date_time_locked is null       )
      and  ( scan_seqno >= :varMinScanSeqno )
      and  ( scan_seqno <= :varMaxScanSeqno )
    group by loan_id, collateral_id, insurance_id) a

Answer 3

this works on SQL Server:

SELECT
    COUNT(*)
    FROM (SELECT
              GroupColumn
              FROM YourTable
              WHERE ...
              GROUP BY GroupColumn
         ) dt

Answer 4

Since you are using Firebird 1.5, there does not seem to be a clean solution.

Firebird 2.0 supports the 'sub-queries in the FROM clause' notation that is a basic requirement for answering such questions - see all the other answers.

So, if you need a clean solution, upgrade to Firebird 2.1. Otherwise, what you have seems to be about as good as you can get.

(Some other DBMS support temporary tables; if Firebird 1.5 does, you could select the initial data into a temporary table, then count the rows in the temporary table, then drop the temporary table again.)