Clever Geek Handbook

Get the number of distinct groups in one SQL query in Firebird 1.5?

In the stored procedure, I looked at the following:

  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 this in one query? And I didn't have a good answer. (So it's more academic. question.) I am not looking for a solution that concatenates IDs like:

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 request this information?

EDIT . Since I haven't apparently made it clear enough, I'm using Firebird v1.5.

+2


source to share


4 answers


Since you are using Firebird 1.5 it looks like there is no clean solution.

Firebird 2.0 supports "FROM subqueries", which is a basic requirement for answering such questions - see all other answers.



So, if you want a clean solution, go for Firebird 2.1. Otherwise, what you have seems to be as good as you can get.

(Some other DBMS support temporary tables, if Firebird 1.5, you can fetch the original data into a temporary table, then count the rows in the temporary table, and then drop the temporary table again.)

+1


source


What's up 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
)
0


source


don't know if more or less efficient, but a subquery will 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
-2


source


this works on SQL Server:

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


source






More articles:


All Articles