FB-3,09 Count(*)

[Henry Olive 5]

I wish everyone a healthy day.

SELECT COUNT(*), DOCNO, DOCTYPE

Returns

1...ABC10....XXX

1...ABC11....YYY

I want to get TOTAL record count of the query result that is the result like below (Total 2 records returns)

2...ABC10....XXX

2...ABC11....YYY

 

How can i get total record count of a query result ?

Thank You

[Serge_G 87]

First what a strange query, a count without Grouping clause !

 

Try this (before fb 3, solution). Really disliking this "subselect" !  

SELECT (SELECT COUNT(1) FROM ATABLE) AS TOT,DOCNO,DOCTYPE FROM ATABLE

 

 

Or use a window (analytical)  function  (here SUM(1) OVER () is the window function)

(Wow now FB3+ have analytical functions 😲)

SELECT SUM(1) OVER () FULLCOUNT, DOCNO,DOCTYPE from ATABLE

 

Edited March 17, 2022 by Serge_G

[Henry Olive 5]

Thank you SO MUCH Serge

[Serge_G 87]

Forgot this one, better  IMHO,  for Firebird<3 using a CTE and JOIN

WITH c as (Select count(1) nb from appose) 
select c.nb,t.* from c full join appose t on 1=1