Q:

How can I calculate number of purchases without using GROUP BY?

I'm working on a table which looks like this:
PURCHASE

PurchaseNo
ReceiptNo

The first three digits in 'Receiptno' represents the receipt book that the receipt is from. How can I write a query to count the number of purchases where at least 10 other purchases have come from the same receipt book?
My first approach was to use GROUP BY on the receipt book digits, but how can I do it without grouping the digits (I can still use GROUP BY elsewhere)?
This would've been my attempt if I grouped by receipt book:
SELECT SUM(total)
FROM (
SELECT COUNT(PURCHASENO) AS total, SUBSTR(RECEIPTNO, 1,3)
FROM PURCHASE
GROUP BY SUBSTR(RECEIPTNO,1,3)
HAVING COUNT(*) > 10);

Any help is greatly appreciated, thanks.

A:

You would use window functions:
SELECT COUNT(*)
FROM (SELECT PURCHASENO AS total,
             SUBSTR(RECEIPTNO, 1, 3) as receipt_book
             COUNT(*) OVER (PARTITION BY SUBSTR(RECEIPTNO, 1, 3)) as receipt_book
      FROM PURCHASE
     ) p
HAVING receipt_book >= 11;

You can also use a subquery:
select count(*)
from purchase p
where (select count(*)
       from purchase p2
       where substr(p2.receiptno, 1, 3) = substr(p.receiptno, 1, 3)
      ) >= 11;

