Hi list,
Say I have a query which grabs some info about a client:
SELECT ClientID
, ClientName
, SUM(InvoiceTotal) AS TotalBilled
, ((SUM(InvoiceTotal) /
(SELECT SUM(InvoiceTotal) FROM Invoices)
) * 100) AS Percentage
FROM Clients c
INNER JOIN Invoices i
ON c.ClientID = i.ClientID
WHERE ClientID = 1
This gives something like:
+----------+------------+-------------+------------+
| ClientID | ClientName | TotalBilled | Percentage |
+----------+------------+-------------+------------+
| 1 | Testco | �123.45 | 40% |
+----------+------------+-------------+------------+
and I can see Testco are responsible for 40% of everything that's ever been
billed. So far so good.
If I have two other clients, one of whom has 50% of the total invoices and
the other has the remaining 10%, Testco's 40% makes it the second-highest
ranked for Percentage.
Is there a way to calculate this rank and return it as part of the query?
+----------+------------+-------------+------------+------+
| ClientID | ClientName | TotalBilled | Percentage | Rank |
+----------+------------+-------------+------------+------+
| 1 | Testco | �123.45 | 40% | 2 |
+----------+------------+-------------+------------+------+
I've thought about giving up on calculating these on-the-fly: instead, I
could add TotalBilled and Percentage columns to the Clients table and update
them via triggers on the Invoices table. I keep changing my mind about
whether this is a good idea or not.
Any pointers appreciated - we're using SQL Server, if that makes a
difference.
Cheers
Jon
____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: [email protected]
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: unknown lmsubst tag argument: ''
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
