Aggregate functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Aggregate functions

Author

Topic

petey84
Starting Member

14 Posts

Posted - 2010-09-22 : 20:51:16

Hi,

I have the following sql code.

SELECT p.FirstName, p.LastName,
(
SELECT Count(s.SalesOrderNumber) as 'Total Sales'
FROM Sales.SalesOrderHeader as s
WHERE s.ContactID = p.ContactID
) AS 'Total Sales'
FROM Person.Contact as p

At the moment the result shows the firsname and lastname of every sale even if that sale is '0'.

I want to only show sales over or equal to '1'.

I try a where clause like this "where 'Total Sales' >= '1', however this did not work.

I think this is due to the Total Sales column being a result of a aggregated function.

Can someone help do I have to create a variable?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-23 : 00:05:48

Your main table is Person.Contact and Total Sales is derived column so the record without any sales will also appear.

Try this:

SELECT p.FirstName, p.LastName,Count(s.SalesOrderNumber)
From Person.Contact p inner join Sales.SalesOrderHeader as s
on s.ContactID = p.ContactID
Group by p.ContactID,p.FirstName, p.LastName

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:26:33

quote:
Originally posted by pk_bohra

Your main table is Person.Contact and Total Sales is derived column so the record without any sales will also appear.

Try this:

SELECT p.FirstName, p.LastName,Count(s.SalesOrderNumber)
From Person.Contact p inner join Sales.SalesOrderHeader as s
on s.ContactID = p.ContactID
Group by p.ContactID,p.FirstName, p.LastName

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

do u really require that ContactID on GROUP BY?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

petey84
Starting Member

14 Posts

Posted - 2010-09-23 : 20:43:00

quote:
Originally posted by pk_bohra

Your main table is Person.Contact and Total Sales is derived column so the record without any sales will also appear.

Try this:

SELECT p.FirstName, p.LastName,Count(s.SalesOrderNumber)
From Person.Contact p inner join Sales.SalesOrderHeader as s
on s.ContactID = p.ContactID
Group by p.ContactID,p.FirstName, p.LastName

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Yes I know I can do it this way, but I am trying subquerys out and wanted to know if I can do it that way.

Anyone know how I can only show total sales > 0?

I have tried the following Where clause "Where Total Sales >= 1", but this does not work as you said it is a derived column.

Can you not run Where clauses on derived columns?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-23 : 23:38:03

quote:

Yes I know I can do it this way, but I am trying subquerys out and wanted to know if I can do it that way.

Anyone know how I can only show total sales > 0?

I have tried the following Where clause "Where Total Sales >= 1", but this does not work as you said it is a derived column.

Can you not run Where clauses on derived columns?

The problem is that where clause is logically processed before select clause. So if you add "where TotalSale >=1" in the query, then when the where clause is processed, it does not know what "TotalSale" is, because at that point, "TotalSale" does not exist yet. The same thing with other columns, if you run this query, it will have error:

SELECT p.FirstName, p.LastName as testName,
(
SELECT Count(s.SalesOrderNumber) as 'Total Sales'
FROM Sales.SalesOrderHeader as s
WHERE s.ContactID = p.ContactID
) AS 'Total Sales'
FROM Person.Contact as p
where testName = 'something'

The query below will work

quote:

select * from
(
-- your query --
SELECT p.FirstName, p.LastName,
(
SELECT Count(s.SalesOrderNumber) as TotalSales
FROM Sales.SalesOrderHeader as s
WHERE s.ContactID = p.ContactID
) AS c
FROM Person.Contact as p
-- your query --
)
as test where TotalSales >= 1

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-24 : 01:09:01

quote:
Originally posted by visakh16

do u really require that ContactID on GROUP BY?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Two people may have same forename and surname. I just wanted to show the two records separately

I am here to learn from Masters and help new bees in learning.

petey84
Starting Member

14 Posts

Posted - 2010-09-24 : 09:23:08

quote:
Originally posted by namman

quote:

Yes I know I can do it this way, but I am trying subquerys out and wanted to know if I can do it that way.

Anyone know how I can only show total sales > 0?

I have tried the following Where clause "Where Total Sales >= 1", but this does not work as you said it is a derived column.

Can you not run Where clauses on derived columns?

The problem is that where clause is logically processed before select clause. So if you add "where TotalSale >=1" in the query, then when the where clause is processed, it does not know what "TotalSale" is, because at that point, "TotalSale" does not exist yet. The same thing with other columns, if you run this query, it will have error:

SELECT p.FirstName, p.LastName as testName,
(
SELECT Count(s.SalesOrderNumber) as 'Total Sales'
FROM Sales.SalesOrderHeader as s
WHERE s.ContactID = p.ContactID
) AS 'Total Sales'
FROM Person.Contact as p
where testName = 'something'

The query below will work
quote:

select * from
(
-- your query --
SELECT p.FirstName, p.LastName,
(
SELECT Count(s.SalesOrderNumber) as TotalSales
FROM Sales.SalesOrderHeader as s
WHERE s.ContactID = p.ContactID
) AS c
FROM Person.Contact as p
-- your query --
)
as test where TotalSales >= 1

Thanks this worked great.

Thanks everyone else for helping too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources