percentage...

Question

Viewing 6 posts - 1 through 5 (of 5 total)

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

abhijeetv SSCommitted Points: 1945 More actions · Answer 1

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.

b_boy SSCertifiable Points: 5145 More actions · Answer 2

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...

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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)