Return TOP 5 Suppliers based on Invoice Volume

Question

Return TOP 5 Suppliers based on Invoice Volume

2Tall

SSChampion

Points: 14644
More actions
October 7, 2009 at 9:31 am

#138549

I have been asked to write a report that amongst other things returns the TOP 5 Suppliers based on the number of invoices they have submitted.
The basic query is as follows:
[Code]
SELECT s.SupplierId,COUNT(pur.PurchaseInvoiceId) AS NumberOfInvoices
FROM PurchaseInvoices AS pur
INNER JOIN Suppliers AS s ON pur.Supplier = s.Supplier
GROUP BY s.SupplierId
ORDER BY NumberOfInvoices DESC,s.SupplierId
[/Code]
I have had a brain freeze, can anyone help defrost!
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

2Tall (10/7/2009)
I have been asked to write a report that amongst other things returns the TOP 5 Suppliers based on the number of invoices they have submitted.
The basic query is as follows:
[Code]
SELECT s.SupplierId,COUNT(pur.PurchaseInvoiceId) AS NumberOfInvoices
FROM PurchaseInvoices AS pur
INNER JOIN Suppliers AS s ON pur.Supplier = s.Supplier
GROUP BY s.SupplierId
ORDER BY NumberOfInvoices DESC,s.SupplierId
[/Code]
I have had a brain freeze, can anyone help defrost!
Thanks,
Phil.

What's wrong with it? Just add a top clause to it? Or am I missing the requirement?

Cheers,

J-F

2Tall SSChampion Points: 14644 More actions · Answer 2

Told you it was brain freeze!

You were correct (as was I!!).

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

Well, Happy to have helped, I wasn't so sure of the requirement, seeing how only the top clause was missing! 😀

Have a nice day,

Cheers,

J-F