SQL tool for MSDE: average

Showing posts with label average. Show all posts

Sunday, March 11, 2012

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?
Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>
|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>
|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP12.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP1
2.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

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)

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

Aggregates and subqueries

Aggregates and subqueries

Tuesday, March 6, 2012

Aggregate Avg > 0

Aggregate Avg > 0

Aggregate Avg > 0

Friday, February 24, 2012

Age Average

SQL tool for MSDE

Blog Archive

About Me