Hi,
I'm currently looking into moving a table to it's own file group with
a view to improving performance.
I want some way to prove that there is a performance improvement so
want to take a performance base line of current activity to compare to
once I've moved the table. I'm going to use
sys.dm_io_virtual_file_stats to get an over view of what IO has been
taken away from the main file group but would also like to get some
table level stats. The number of times the table is accessed should
not change so I was wondering what table level data is available for
me to show an improvement. That is, if there is an improvement!
Cheers
SimonI think you can do one thing here:
Just note down the time taken(duration) for queries that are getting fired
on this table with existing setup during off peak or peak hours as per ur
envt. and then run them(queries or procedures) again once u r done with
movement and compare the stats.
Manu
"simon.pope.public@.gmail.com" wrote:
> Hi,
> I'm currently looking into moving a table to it's own file group with
> a view to improving performance.
> I want some way to prove that there is a performance improvement so
> want to take a performance base line of current activity to compare to
> once I've moved the table. I'm going to use
> sys.dm_io_virtual_file_stats to get an over view of what IO has been
> taken away from the main file group but would also like to get some
> table level stats. The number of times the table is accessed should
> not change so I was wondering what table level data is available for
> me to show an improvement. That is, if there is an improvement!
> Cheers
> Simon
>|||On 13 Sep, 20:40, manu <m...@.discussions.microsoft.com> wrote:
> I think you can do one thing here:
> Just note down the time taken(duration) for queries that are getting fired
> on this table with existing setup during off peak or peak hours as per ur
> envt. and then run them(queries or procedures) again once u r done with
> movement and compare the stats.
> Manu
>
> "simon.pope.pub...@.gmail.com" wrote:
> > Hi,
> > I'm currently looking into moving a table to it's own file group with
> > a view to improving performance.
> > I want some way to prove that there is a performance improvement so
> > want to take a performance base line of current activity to compare to
> > once I've moved the table. I'm going to use
> > sys.dm_io_virtual_file_stats to get an over view of what IO has been
> > taken away from the main file group but would also like to get some
> > table level stats. The number of times the table is accessed should
> > not change so I was wondering what table level data is available for
> > me to show an improvement. That is, if there is an improvement!
> > Cheers
> > Simon- Hide quoted text -
> - Show quoted text -
Cheers manu, I think you're right. It is a performance enhancement
after all and so time taken to query data in the table is as good an
indicator as any.
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
performance (distinct and group )
hi!
I have a doubt when the performance of some commands distinct and group by,
which is faster
some times group by is faster than distinct, somebody knows of something?
tanks
joaojoao
Some comments I have cut and pasted from a programming
good practices document
Carefully evaluate whether your query needs the DISTINCT
clause or not. The DISTINCT clause slows down virtually
every query it is in. Some developers automatically add
this clause to every one of their SELECT statements, even
when it is not necessary. This is a bad habit that should
be stopped. In addition, keep in mind that in some cases,
duplicate results in a query are not a problem. If this is
the case, then don't use a DISTINCT clause.
Don't use DISTINCT or ORDER BY in your SELECT statements
unless you really need them. Both options can add a lot of
additional overhead to your query, and they aren't always
needed for your application.
If your SELECT statement contains a HAVING clause, write
your query so that the WHERE clause does most of the work
(removing undesired rows) instead of the HAVING clause do
the work of removing undesired rows. Using the WHERE
clause appropriately can eliminate unnecessary rows before
they get to the GROUP BY and HAVING clause, saving some
unnecessary work, and boosting performance.
For example, in a SELECT statement with WHERE, GROUP BY,
and HAVING clauses, here's what happens. First, the WHERE
clause is used to select the appropriate rows that need to
be grouped. Next, the GROUP BY clause divides the rows
into sets of grouped rows, and then aggregates their
values. And last, the HAVING clause then eliminates
undesired aggregated groups. If the WHERE clause is used
to eliminate as many of the undesired rows as possible,
this means the GROUP BY and the HAVING clauses will have
less work to do, boosting the overall performance of the
query.
The GROUP BY clause can be used with or without an
aggregate function. But if you want optimum performance,
don't use the GROUP BY clause without an aggregate
function. This is because you can accomplish the same end
result by using the DISTINCT option instead, and it is
faster.
For example, you could write your query two different
ways:
USE Northwind
SELECT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
GROUP BY OrderID
or
USE Northwind
SELECT DISTINCT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
Both of the above queries produce the same results, but
the second one will use less resources and perform faster.
Hope this helps
John|||John Bandettini wrote:
<snip>
> SELECT OrderID
> FROM [Order Details]
> WHERE UnitPrice > 10
> GROUP BY OrderID
> or
> SELECT DISTINCT OrderID
> FROM [Order Details]
> WHERE UnitPrice > 10
> Both of the above queries produce the same results, but
> the second one will use less resources and perform faster.
John, when I run these queries, I get exactly the same query plan. This
is what I expected. I think nowadays SQL-Server is smart enough to
determine that these two queries produce the same result, and therefore
can be handled with the same (optimum) query plan.
Gert-Jan|||In addition...
If you get a significant difference between the two, you might want to communicate this to MS. If
you have a repro, you can post it here. These two operations are the same, semantically, and if the
optimizer produces a worse plan for distinct then we have found a weakness in the optimizer.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F9440F5.9E25C9C2@.toomuchspamalready.nl...
> John Bandettini wrote:
> <snip>
> > SELECT OrderID
> > FROM [Order Details]
> > WHERE UnitPrice > 10
> > GROUP BY OrderID
> > or
> >
> > SELECT DISTINCT OrderID
> > FROM [Order Details]
> > WHERE UnitPrice > 10
> >
> > Both of the above queries produce the same results, but
> > the second one will use less resources and perform faster.
> John, when I run these queries, I get exactly the same query plan. This
> is what I expected. I think nowadays SQL-Server is smart enough to
> determine that these two queries produce the same result, and therefore
> can be handled with the same (optimum) query plan.
> Gert-Jan
I have a doubt when the performance of some commands distinct and group by,
which is faster
some times group by is faster than distinct, somebody knows of something?
tanks
joaojoao
Some comments I have cut and pasted from a programming
good practices document
Carefully evaluate whether your query needs the DISTINCT
clause or not. The DISTINCT clause slows down virtually
every query it is in. Some developers automatically add
this clause to every one of their SELECT statements, even
when it is not necessary. This is a bad habit that should
be stopped. In addition, keep in mind that in some cases,
duplicate results in a query are not a problem. If this is
the case, then don't use a DISTINCT clause.
Don't use DISTINCT or ORDER BY in your SELECT statements
unless you really need them. Both options can add a lot of
additional overhead to your query, and they aren't always
needed for your application.
If your SELECT statement contains a HAVING clause, write
your query so that the WHERE clause does most of the work
(removing undesired rows) instead of the HAVING clause do
the work of removing undesired rows. Using the WHERE
clause appropriately can eliminate unnecessary rows before
they get to the GROUP BY and HAVING clause, saving some
unnecessary work, and boosting performance.
For example, in a SELECT statement with WHERE, GROUP BY,
and HAVING clauses, here's what happens. First, the WHERE
clause is used to select the appropriate rows that need to
be grouped. Next, the GROUP BY clause divides the rows
into sets of grouped rows, and then aggregates their
values. And last, the HAVING clause then eliminates
undesired aggregated groups. If the WHERE clause is used
to eliminate as many of the undesired rows as possible,
this means the GROUP BY and the HAVING clauses will have
less work to do, boosting the overall performance of the
query.
The GROUP BY clause can be used with or without an
aggregate function. But if you want optimum performance,
don't use the GROUP BY clause without an aggregate
function. This is because you can accomplish the same end
result by using the DISTINCT option instead, and it is
faster.
For example, you could write your query two different
ways:
USE Northwind
SELECT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
GROUP BY OrderID
or
USE Northwind
SELECT DISTINCT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
Both of the above queries produce the same results, but
the second one will use less resources and perform faster.
Hope this helps
John|||John Bandettini wrote:
<snip>
> SELECT OrderID
> FROM [Order Details]
> WHERE UnitPrice > 10
> GROUP BY OrderID
> or
> SELECT DISTINCT OrderID
> FROM [Order Details]
> WHERE UnitPrice > 10
> Both of the above queries produce the same results, but
> the second one will use less resources and perform faster.
John, when I run these queries, I get exactly the same query plan. This
is what I expected. I think nowadays SQL-Server is smart enough to
determine that these two queries produce the same result, and therefore
can be handled with the same (optimum) query plan.
Gert-Jan|||In addition...
If you get a significant difference between the two, you might want to communicate this to MS. If
you have a repro, you can post it here. These two operations are the same, semantically, and if the
optimizer produces a worse plan for distinct then we have found a weakness in the optimizer.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F9440F5.9E25C9C2@.toomuchspamalready.nl...
> John Bandettini wrote:
> <snip>
> > SELECT OrderID
> > FROM [Order Details]
> > WHERE UnitPrice > 10
> > GROUP BY OrderID
> > or
> >
> > SELECT DISTINCT OrderID
> > FROM [Order Details]
> > WHERE UnitPrice > 10
> >
> > Both of the above queries produce the same results, but
> > the second one will use less resources and perform faster.
> John, when I run these queries, I get exactly the same query plan. This
> is what I expected. I think nowadays SQL-Server is smart enough to
> determine that these two queries produce the same result, and therefore
> can be handled with the same (optimum) query plan.
> Gert-Jan
Saturday, February 25, 2012
perform aggregate function & group by
Hello, anyone can help?
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>
Perform aggregate against group value
Is there a way that I can perform my aggregate function agains the value in
group row instead of details row?
Because I do not show numeric values in the details but group row.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET DevelopersDid you try the Previous aggregate function? Just place it into the group
header like =Previous(Fields!Country.Value) and it should work. Note: the
previous function has only one argument.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Is there a way that I can perform my aggregate function agains the value
> in group row instead of details row?
> Because I do not show numeric values in the details but group row.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
>|||Hi Robert,
I tried Previous as you suggested but this time I endup with empty
datacell..
However, please excuse me that I was not clear enough previously, I want to
perform this aggregate against the group in the footer. So I tried to use
the full previous function as described in the help "Previous(Expression,
AggFunction, PreviousScope, AggScope)" but it basically fails as you've
mentioned.
Anyway, do you have a solution for me?
I would like to sum my group values in the footer.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET Developers
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
> Did you try the Previous aggregate function? Just place it into the group
> header like =Previous(Fields!Country.Value) and it should work. Note: the
> previous function has only one argument.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "SevDer" <sevder@.newsgroup.nospam> wrote in message
> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>|||> I would like to sum my group values in the footer.
Maybe I'm missing something, but assuming you group on
=Fields!FieldName.Value, just adding an expression like
=Sum(Fields!FieldName.Value) in the table footer should sum the group
values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:%23rqJeUw2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Hi Robert,
> I tried Previous as you suggested but this time I endup with empty
> datacell..
> However, please excuse me that I was not clear enough previously, I want
> to perform this aggregate against the group in the footer. So I tried to
> use the full previous function as described in the help
> "Previous(Expression, AggFunction, PreviousScope, AggScope)" but it
> basically fails as you've mentioned.
> Anyway, do you have a solution for me?
> I would like to sum my group values in the footer.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
>> Did you try the Previous aggregate function? Just place it into the group
>> header like =Previous(Fields!Country.Value) and it should work. Note: the
>> previous function has only one argument.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "SevDer" <sevder@.newsgroup.nospam> wrote in message
>> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>>
>
group row instead of details row?
Because I do not show numeric values in the details but group row.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET DevelopersDid you try the Previous aggregate function? Just place it into the group
header like =Previous(Fields!Country.Value) and it should work. Note: the
previous function has only one argument.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Is there a way that I can perform my aggregate function agains the value
> in group row instead of details row?
> Because I do not show numeric values in the details but group row.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
>|||Hi Robert,
I tried Previous as you suggested but this time I endup with empty
datacell..
However, please excuse me that I was not clear enough previously, I want to
perform this aggregate against the group in the footer. So I tried to use
the full previous function as described in the help "Previous(Expression,
AggFunction, PreviousScope, AggScope)" but it basically fails as you've
mentioned.
Anyway, do you have a solution for me?
I would like to sum my group values in the footer.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET Developers
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
> Did you try the Previous aggregate function? Just place it into the group
> header like =Previous(Fields!Country.Value) and it should work. Note: the
> previous function has only one argument.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "SevDer" <sevder@.newsgroup.nospam> wrote in message
> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>|||> I would like to sum my group values in the footer.
Maybe I'm missing something, but assuming you group on
=Fields!FieldName.Value, just adding an expression like
=Sum(Fields!FieldName.Value) in the table footer should sum the group
values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:%23rqJeUw2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Hi Robert,
> I tried Previous as you suggested but this time I endup with empty
> datacell..
> However, please excuse me that I was not clear enough previously, I want
> to perform this aggregate against the group in the footer. So I tried to
> use the full previous function as described in the help
> "Previous(Expression, AggFunction, PreviousScope, AggScope)" but it
> basically fails as you've mentioned.
> Anyway, do you have a solution for me?
> I would like to sum my group values in the footer.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
>> Did you try the Previous aggregate function? Just place it into the group
>> header like =Previous(Fields!Country.Value) and it should work. Note: the
>> previous function has only one argument.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "SevDer" <sevder@.newsgroup.nospam> wrote in message
>> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>>
>
Monday, February 20, 2012
Percentage of occurrences of a value
I want to be able to retrieve the percentage that one value occurs in a field in a group. For example, a field that has either "Yes" or "No", if there are 3 "Yes" values and 1 "No" value (using mixed real and fake SQL..)
SELECT DATEPART(m,datefield), PECENTAGEOFAVALUE(YesNoField, 'Yes')
FROM sampletable
GROUP BY DATEPART(m,datefield)
Result
75
How can I do this? I know I can get the denominator by just doing a COUNT(*), but how do I count "Yes" only?
Thanks,
Kayda
Here it is..
Code Snippet
Create Table #data (
[Id] int ,
[Response] Char
);
Insert Into #data Values('1','Y');
Insert Into #data Values('2','N');
Insert Into #data Values('3','Y');
Insert Into #data Values('4','Y');
Insert Into #data Values('5','N');
Insert Into #data Values('6','N');
Insert Into #data Values('7','N');
Select
Isnull(Sum(Case When Response='Y' Then 1 End),0) / Sum(1.0) *100,
Isnull(Sum(Case When Response='N' Then 1 End),0) / Sum(1.0) *100
From
#Data
Code Snippet
--For Your query
SELECT
DATEPART(m,datefield)
Isnull(Sum(Case When YesNoField='Yes' Then 1 End),0) / Sum(1.0) * 100 PecentageofYes,
Isnull(Sum(Case When YesNoField='No' Then 1 End),0) / Sum(1.0) * 100 PecentageofNo
FROM
sampletable
GROUP BY
DATEPART(m,datefield)
|||
Here is an example:
Code Snippet
create table #t (
c1 datetime not null,
c2 char(1) not null
);
insert into #t values('20070101','y');
insert into #t values('20070115','n');
insert into #t values('20070125','y');
insert into #t values('20070205','y');
insert into #t values('20070217','n');
insert into #t values('20070225','n');
insert into #t values('20070227','n');
;with sums
as
(
select
convert(char(6), c1, 112) as ym,
sum(case when c2 = 'y' then 1 else 0 end) as sum_y,
sum(case when c2 = 'n' then 1 else 0 end) as sum_n,
count(*) over() as cnt
from
#t
group by
convert(char(6), c1, 112)
)
select
ym,
(sum_y * 100.00) / nullif(cnt, 0) as avg_y,
(sum_n * 100.00) / nullif(cnt, 0) as avg_n
from
sums
order by
ym
drop table #t
go
AMB
Percentage Expression
Hi,
How do I calculate a % for the Group Totals ?
%age = ((CurrentYear - PreviousYear) / PreviousYear)
Thankstry sum( ((CurrentYear - PreviousYear) / PreviousYear) )|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages
How do I calculate a % for the Group Totals ?
%age = ((CurrentYear - PreviousYear) / PreviousYear)
Thankstry sum( ((CurrentYear - PreviousYear) / PreviousYear) )|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages
Subscribe to:
Posts (Atom)