hey everyone,

I'm trying to get my head around this Question but it is not making that much sense ..

Each receipt is issued from a receipt book whose number is encoded in the first
three digits of the ReceiptNo field in the PURCHASE table. For example, the
receipt numbered 454333 was issued from receipt book number 454.
Write a SQL statement to count the number of purchases for which there has been at
least 10 other purchases issued from the same receipt book.

Hint: for each purchase p in the PURCHASE table, you will need to go over all
the other purchases and find the ones with a ReceiptNo that starts with the same
3 digits as in p’s ReceiptNo.

The only way I'm thinking about to solve this is to go with a function-based index.. Is there any other way of solving it?

I know how to write a SQL statement that counts how many purchases but I'm not too sure what to put in the where clause.. .

Hear from you folks...

Member Avatar

Actually... No need for a Where clause (unless there are other conditions not specified in your post).
It's actually quite easy.
You'll need;
1. the equation (you COULD use a function, but it's not required) to get your receipt book based on the receipt number
2. a "Group by..."
3. and a "Having Count (*) > 9"
4. you may also require an "Order by..." since the "Group by" doesn't always guarantee the correct order

Group by...
Having Count (*)...
Order by...
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.