SQL tool for MSDE: calculate

Showing posts with label calculate. Show all posts

Sunday, March 11, 2012

Aggregation functon

Hi,
I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
And now I need
SELECT * FROM Table WHERE
row is equal to row which was used to calculate output of result from
SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
It suffices me this
SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
but it is not legal.
Thank for your suggestionsI think the easiest way to do this would be :
SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
FROM Table
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
This will return you the first line only where SQRT() is the biggest. Mind
that if you want to have ALL lines where SQRT reaches it's maximum, then
you'll want this :
SELECT SQRT(X1*X2+Y1*Y2), *
FROM Table
WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
FROM Table)
Good luck
Roby
"B.J." wrote:

> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||something like this:
select *
from table
where pk = (select top 1 pk from table order by SQRT(X1*X2+Y1*Y2) desc)
where 'pk' is the table's primary key.
dean
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:67E9D409-A13A-4982-A7EA-74A946A42542@.microsoft.com...
> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||SELECT x1, x2, y1, y2, SQRT(x1*x2 + y1*y2)
FROM YourTable
WHERE x1*x2 + y1*y2 =
(SELECT MAX(x1*x2 + y1*y2)
FROM YourTable)
The SQRT() function is redundant in the subquery so I've left it out
here to save a few cycles.
David Portas
SQL Server MVP
--|||Another way to get all rows with the maximum X1*X2+Y1*Y2 is
SELECT TOP 1 WITH TIES SQRT(X1*X2+Y1*Y2), *
FROM T
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
Steve Kass
Drew University
deroby wrote:
>I think the easiest way to do this would be :
>SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
> FROM Table
>ORDER BY SQRT(X1*X2+Y1*Y2) DESC
>This will return you the first line only where SQRT() is the biggest. Mind
>that if you want to have ALL lines where SQRT reaches it's maximum, then
>you'll want this :
>SELECT SQRT(X1*X2+Y1*Y2), *
> FROM Table
> WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
> FROM Table)
>Good luck
>Roby
>
>"B.J." wrote:
>
>

Thursday, March 8, 2012

Aggregate on a Calculated Measure

I am trying to calculate aggregate on a calculate measure. See below for code. I get an error saying I cannot aggregate over calculated member in the measures dimension. I need to calculate MTD and YTD values for a calculated member. Is there any way I can do this?

Any help will be appreciated.

Thanks

Ann

Aggregate

(

PeriodsToDate

(

[Time].[Time].[Month],

[Time].[Time].CurrentMember

(

SUM({[Order Type].[Order Type].&[Membership Renewal Order],[Order Type].[Order Type].&[Membership Renewal with Autoship Order],[Order Type].[Order Type].&[New Membership Order],[Order Type].[Order Type].&[New Membership with Autoship Order]},[Measures].[Sale Amount]))

Try replacing the "Aggregate" function in your statement with "SUM". Since you are not attempting to aggregate a "Distinct Count" or some other semi-additive measure this should give you the result you are looking for. If you want create MTD, YTD or other period calculations that work with multiple measures your best bet is to use a "shell dimension" that will allow the creation of members using the "Aggregate" function. You can use the Business Intelligence Wizard to add time calculations, which will automate the process of creating the shell dimension for you. There are some bugs in the current version, but there is a workaround posted at the link below and SP1 includes a fix that addresses the issue.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;912136

HTH,

- Steve

Tuesday, March 6, 2012

Aggregate Avg > 0

Is it possible to calculate an aggregate average specifically for all values
> 0?Mike
Done on Northwind Database
select avg(unitprice),orderid
from [order details]
group by orderid
having avg(unitprice)>20--change it
"Mike" <mike@.hello.com> wrote in message
news:%23QqEX2MnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Is it possible to calculate an aggregate average specifically for all
> values
> > 0?
>|||> "Mike" <mike@.hello.com> wrote in message
> news:%23QqEX2MnFHA.3448@.TK2MSFTNGP12.phx.gbl...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OQmOw%23MnFHA.1412@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to calculate an aggregate average specifically for all
>> values > 0?
> Mike
> Done on Northwind Database
> select avg(unitprice),orderid
> from [order details]
> group by orderid
> having avg(unitprice)>20--change it
Sorry, I didn't clarify exactly what I was looking for.
Say we have Table1:
ID |Count | Average
===========1 | 1 | 7.2
2 | 2 | 6.8
3 | 2 | 0
4 | 5 | 8.4
5 | 2 | 0
I want to produce the following aggregate totals (all in the one query):
CountSum = 1+2+2+5+2 (This is obviously very easy)
and
AverageAvg = (7.2+6.8+8.4)/3
i.e. Note that the average function only averages results greater than zero.
That's what I'm looking for.|||Mike
create table #test
(
[id]int not null primary key,
[count] int not null,
[Average] decimal(5,2) not null
)
go
insert into #test values (1,1,7.2)
insert into #test values (2,2,6.2)
insert into #test values (3,2,0)
insert into #test values (4,5,8.4)
insert into #test values (5,2,0)
select avg(average) from #test
where average>0
"Mike" <mike@.hello.com> wrote in message
news:%23P%23F$FNnFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> "Mike" <mike@.hello.com> wrote in message
>> news:%23QqEX2MnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OQmOw%23MnFHA.1412@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to calculate an aggregate average specifically for all
>> values > 0?
>>
>> Mike
>> Done on Northwind Database
>> select avg(unitprice),orderid
>> from [order details]
>> group by orderid
>> having avg(unitprice)>20--change it
> Sorry, I didn't clarify exactly what I was looking for.
> Say we have Table1:
> ID |Count | Average
> ===========> 1 | 1 | 7.2
> 2 | 2 | 6.8
> 3 | 2 | 0
> 4 | 5 | 8.4
> 5 | 2 | 0
> I want to produce the following aggregate totals (all in the one query):
> CountSum = 1+2+2+5+2 (This is obviously very easy)
> and
> AverageAvg = (7.2+6.8+8.4)/3
> i.e. Note that the average function only averages results greater than
> zero. That's what I'm looking for.
>|||Select AVG(<Yourcolumn>) from sometable where <Yourcolumn> > 0 ?
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Mike" wrote:
> > "Mike" <mike@.hello.com> wrote in message
> > news:%23QqEX2MnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OQmOw%23MnFHA.1412@.TK2MSFTNGP09.phx.gbl...
> >> Is it possible to calculate an aggregate average specifically for all
> >> values > 0?
> >>
> >
> > Mike
> > Done on Northwind Database
> >
> > select avg(unitprice),orderid
> > from [order details]
> > group by orderid
> > having avg(unitprice)>20--change it
> Sorry, I didn't clarify exactly what I was looking for.
> Say we have Table1:
> ID |Count | Average
> ===========> 1 | 1 | 7.2
> 2 | 2 | 6.8
> 3 | 2 | 0
> 4 | 5 | 8.4
> 5 | 2 | 0
> I want to produce the following aggregate totals (all in the one query):
> CountSum = 1+2+2+5+2 (This is obviously very easy)
> and
> AverageAvg = (7.2+6.8+8.4)/3
> i.e. Note that the average function only averages results greater than zero.
> That's what I'm looking for.
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ethdQNNnFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Mike
> create table #test
> (
> [id]int not null primary key,
> [count] int not null,
> [Average] decimal(5,2) not null
> )
> go
> insert into #test values (1,1,7.2)
> insert into #test values (2,2,6.2)
> insert into #test values (3,2,0)
> insert into #test values (4,5,8.4)
> insert into #test values (5,2,0)
>
> select avg(average) from #test
> where average>0
Yes, that is a way of calculating the average, but it isn't in the same
query as the SUM aggregate function. I want to find a quick way of doing the
AVG > 0 function alongside the SUM function. If this isn't possible then so
be it.|||"Jens Süßmeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:29C15C0D-7617-403B-9203-7CF535BD4799@.microsoft.com...
> Select AVG(<Yourcolumn>) from sometable where <Yourcolumn> > 0 ?
> --
> HTH, Jens Suessmeyer.
Yes, that is the easy but long-winded version. I want to achieve this in
once single query alongside the SUM function.
e.g.
SELECT SUM(NBSTWPremSum) AS NBSTWPremSumTotal, AVG(SELECT NBSTWPremAvg WHERE
NBSTWPremAvg >0) AS NBSTWPremAvgTotal FROM tblTempOpStats
The above is illegal but hopefully gives an indicator of what I'm trying to
achieve.|||Mike
Have you read your own posts? Did
mention about SUM function NOTHING.
"Mike" <mike@.hello.com> wrote in message
news:u7iQORNnFHA.1968@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ethdQNNnFHA.3120@.TK2MSFTNGP09.phx.gbl...
>> Mike
>> create table #test
>> (
>> [id]int not null primary key,
>> [count] int not null,
>> [Average] decimal(5,2) not null
>> )
>> go
>> insert into #test values (1,1,7.2)
>> insert into #test values (2,2,6.2)
>> insert into #test values (3,2,0)
>> insert into #test values (4,5,8.4)
>> insert into #test values (5,2,0)
>>
>> select avg(average) from #test
>> where average>0
> Yes, that is a way of calculating the average, but it isn't in the same
> query as the SUM aggregate function. I want to find a quick way of doing
> the AVG > 0 function alongside the SUM function. If this isn't possible
> then so be it.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:egBdAcNnFHA.860@.TK2MSFTNGP12.phx.gbl...
> Mike
> Have you read your own posts? Did
> mention about SUM function NOTHING.
I said:
"I want to produce the following aggregate totals (all in the one query):
CountSum = 1+2+2+5+2 (This is obviously very easy)
and
AverageAvg = (7.2+6.8+8.4)/3"|||I think this is what you are after:
create table #test
(
[id] int not null primary key,
[count] int not null,
[average] decimal(5,2) not null
)
go
insert into #test values (1,1,7.2)
insert into #test values (2,2,6.2)
insert into #test values (3,2,0)
insert into #test values (4,5,8.4)
insert into #test values (5,2,0)
select sum(count), avg(case when average > 0 then average else null end)
from #test
drop table #test
R
"Mike" <mike@.hello.com> wrote in message
news:%23uk$QrNnFHA.2916@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:egBdAcNnFHA.860@.TK2MSFTNGP12.phx.gbl...
> > Mike
> > Have you read your own posts? Did
> > mention about SUM function NOTHING.
> I said:
> "I want to produce the following aggregate totals (all in the one query):
> CountSum = 1+2+2+5+2 (This is obviously very easy)
> and
> AverageAvg = (7.2+6.8+8.4)/3"
>|||You can use a non-correlated subquery to return the desired average
independently of the SUM. For example:
SELECT
SUM(NBSTWPremSum) AS NBSTWPremSumTotal,
(SELECT
AVG(NBSTWPremAvg)
FROM tblTempOpStats
WHERE NBSTWPremAvg > 0) AS NBSTWPremAvgTotal
FROM NBSTWPremSumTotal
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mike@.hello.com> wrote in message
news:eZL1yVNnFHA.3936@.TK2MSFTNGP10.phx.gbl...
> "Jens Süßmeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message
> news:29C15C0D-7617-403B-9203-7CF535BD4799@.microsoft.com...
>> Select AVG(<Yourcolumn>) from sometable where <Yourcolumn> > 0 ?
>> --
>> HTH, Jens Suessmeyer.
> Yes, that is the easy but long-winded version. I want to achieve this in
> once single query alongside the SUM function.
> e.g.
> SELECT SUM(NBSTWPremSum) AS NBSTWPremSumTotal, AVG(SELECT NBSTWPremAvg
> WHERE NBSTWPremAvg >0) AS NBSTWPremAvgTotal FROM tblTempOpStats
> The above is illegal but hopefully gives an indicator of what I'm trying
> to achieve.
>|||On Tue, 9 Aug 2005 12:00:31 +0100, Mike wrote:
(snip)
>Sorry, I didn't clarify exactly what I was looking for.
>Say we have Table1:
>ID |Count | Average
>===========>1 | 1 | 7.2
>2 | 2 | 6.8
>3 | 2 | 0
>4 | 5 | 8.4
>5 | 2 | 0
>I want to produce the following aggregate totals (all in the one query):
>CountSum = 1+2+2+5+2 (This is obviously very easy)
>and
>AverageAvg = (7.2+6.8+8.4)/3
Hi Mike,
SELECT SUM(Count),
AVG(CASE WHEN Average > 0 THEN Average END)
FROM MyTable
If the Average column can never be negative (i.e. it is 0 -and has to be
excluded- or >0), then you can use this shorter version:
SELECT SUM(Count),
AVG(NULLIF(Average, 0))
FROM MyTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||OR ...
What about ...
SELECT SUM(Count),
AVG(Average)
FROM MyTable
WHERE Average <> 0
That seems easier to me, but I haven't seen the entire post, so I am not
sure.
HTH.
Daren Bieniek, MCDBA, MCSE
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ou4if1p8d2gpjasotrq9vpmecu49judjh2@.4ax.com...
> On Tue, 9 Aug 2005 12:00:31 +0100, Mike wrote:
> (snip)
> >Sorry, I didn't clarify exactly what I was looking for.
> >
> >Say we have Table1:
> >
> >ID |Count | Average
> >===========> >1 | 1 | 7.2
> >2 | 2 | 6.8
> >3 | 2 | 0
> >4 | 5 | 8.4
> >5 | 2 | 0
> >
> >I want to produce the following aggregate totals (all in the one query):
> >
> >CountSum = 1+2+2+5+2 (This is obviously very easy)
> >
> >and
> >
> >AverageAvg = (7.2+6.8+8.4)/3
> Hi Mike,
> SELECT SUM(Count),
> AVG(CASE WHEN Average > 0 THEN Average END)
> FROM MyTable
> If the Average column can never be negative (i.e. it is 0 -and has to be
> excluded- or >0), then you can use this shorter version:
> SELECT SUM(Count),
> AVG(NULLIF(Average, 0))
> FROM MyTable
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Saturday, February 25, 2012

Aggregate (SUM) a column and then use the Result?

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

What about using an Execute SQL task in the control flow to put that value into a variable; then you can use that variable in the data flow and perform the calculation using a derived column. This thread explains how to run queries against an excel file: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1

|||

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

|||Hi Jamie,

I understand the flow:

Load Data
Calculate the Sum
Calculate new columns based on the sum (mostly count_value/total to get the rate)
Insert the final data into database.

What I cannot figure out is how to do the sum as an aggregate and then use it to do the computation.

Fawad
|||Hi Jamie,

I understand the flow that I require:

Import data from Excel
Sum the one column
Use this sum to calculate new column (mostly rates)
Load in the database

What I cannot figure out is how to use the result of the sum from the aggregate task.

Fawad
|||

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,

I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB

What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.

Fawad
|||

Fawad wrote:

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,
I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB
What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.
Fawad

Right. So Rafael's suggestion is exactly how you should attempt to do this.

-Jamie

|||

Fawad,

My sugestion was to use an Execute SQL task (control flow) to get the SUM value into a variable; it would be a query like Select Sum(yourcolumn) from [YourExcelSheet]. this step has to be done before the dataflow. Then the dataflow would have that value avilable and it could be used in a derived column.

Friday, February 24, 2012

Age Average

I have to calculate the average age in a group.
What is the best way to calculate the average age where I have the
individual's birth date?
Thanks in advance!SELECT AVG(DATEDIFF(DAY,birth_date,CURRENT_TIME
STAMP))/365.25
FROM YourTable
GROUP BY ...
David Portas
SQL Server MVP
--|||I would use
select datediff(mm, yourDOB, getdate())/12.0
Perayu
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:F3FCEC3B-D90D-42C5-B42D-179206660285@.microsoft.com...
>I have to calculate the average age in a group.
> What is the best way to calculate the average age where I have the
> individual's birth date?
> Thanks in advance!|||wnfisba wrote:
> I have to calculate the average age in a group.
> What is the best way to calculate the average age where I have the
> individual's birth date?
> Thanks in advance!
More information please...
Post your table DDL and more detailed specs of what you're trying to
accomplish. SQL has an AVG() function to calculate averages based on a
set of data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||As an alternative to getting the average age, you could get the average
birthdate. That could be more useful in some circumstances.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:F3FCEC3B-D90D-42C5-B42D-179206660285@.microsoft.com...
>I have to calculate the average age in a group.
> What is the best way to calculate the average age where I have the
> individual's birth date?
> Thanks in advance!|||Just be sure it's birthdate, not birth(month/day). I can see the headline:
"Average American born in early July, study shows." ;)
Steve Kass
Drew University
Paul Pedersen wrote:

>As an alternative to getting the average age, you could get the average
>birthdate. That could be more useful in some circumstances.
>
>"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
>news:F3FCEC3B-D90D-42C5-B42D-179206660285@.microsoft.com...
>
>
>|||Ha ha! So I'm average after all.
"Steve Kass" <skass@.drew.edu> wrote in message
news:OcEysRNsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Just be sure it's birthdate, not birth(month/day). I can see the
> headline:
> "Average American born in early July, study shows." ;)
> Steve Kass
> Drew University
> Paul Pedersen wrote:
>

Sunday, March 11, 2012

Aggregation functon

Thursday, March 8, 2012

Aggregate on a Calculated Measure

Tuesday, March 6, 2012

Aggregate Avg > 0

Aggregate Avg > 0

Aggregate Avg > 0

Saturday, February 25, 2012

Aggregate (SUM) a column and then use the Result?

Friday, February 24, 2012

Age Average

SQL tool for MSDE

Blog Archive

About Me