September 17, 2008 at 4:21 am
Hello
Does anyone know how I can calculate the % value of a result set. e.g. I table that consist of orders which amount to over $300, and I want to caluclate the % of all orders with total of over 300 against the total amount of orders within a date range, can anyone help?
September 17, 2008 at 7:47 am
If I am getting your requirement correctly then You can use CTE for this...
First create a CTE with sum(orders) field say alias as 'yourField' for over $300.
while using that CTE calculate the yourField/SUM(Orders) *100 group by your main field on date condition.
September 17, 2008 at 7:53 am
abhijeetv (9/17/2008)
First create a CTE with sum(orders) field say alias as 'yourField' for over $300.
What do you mean by CTE?
because I used:
USE db
declare @Threemore int;
declare @all int;
set @Threemore = (SELECT COUNT(ORDERID) FROM T_OrderHeader WHERE ORDERTOTAL >=300 AND OrderDate BETWEEN '2008-09-01' AND GETDATE ());
set @all = (SELECT COUNT(ORDERID) FROM T_OrderHeader WHERE OrderDate BETWEEN '2008-09-01' AND GETDATE ())
SELECT(CAST(@Threemore AS FLOAT)/CAST(@all AS FLOAT)*100) AS [% Orders over £300]
--
The only I am having is tryin to group the aggregate results to appear as a pivot table with 02/09/2008, 03/09/2008 as rows...
September 17, 2008 at 7:57 am
You only need a CTE if you do not already have the Order Totals. If you do, then this is all you need:
SELECT
SUM(Case
When Order_Total > 300.00 Then Order_Total
Else 0.00 End)
/ SUM(Order_Total)
FROM Orders
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2008 at 8:02 am
If you do not have the Order totals ahead of time, then you can do something like this:
WITH cteOrders As
(Select SUM(Detail_Cost) as Order_Total
From OrderDetail
Group By OrderID)
SELECT
SUM(Case
When Order_Total > 300.00 Then Order_Total
Else 0.00 End)
/ SUM(Order_Total)
FROM cteOrders
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2008 at 8:09 am
will the results appear as a pivot table with 02/09/2008, 03/09/2008 as rows?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy