select sum() as total where (field > total)

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

General SQL Server Forums

New to SQL Server Programming

select sum() as total where (field > total)

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-08-28 : 18:06:25

I'm trying to set a where clause into my select statement. I keep getting an error message saying total is an invalid column. What do I have to do?

Thanks in advance.

Here's my select statement:


SELECT s.storeName,
       s.storeID,
       s.peakLimit,
       o.deliveryDate,
	orderState,
       sum ((OrderItemType.dollarValue*OrderItem.quantity)) as total

FROM .....

WHERE   o.deliveryDate > GetDate()+3
        and s.storeID = s.storeID
        and (s.peakLimit < total)

GROUP BY s.storeName,
       s.storeID,
       s.peakLimit,
       o.deliveryDate,
	orderState,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 18:08:33

Wait a minute...
You are grouping on the peakLimit column among several others, and still you want to check if sum for that combination is less than the total?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 18:10:19

[code]SELECT storeName,
storeID,
peakLimit,
deliveryDate,
orderState,
total
from (
SELECT s.storeName,
s.storeID,
s.peakLimit,
o.deliveryDate,
orderState,
sum(OrderItemType.dollarValue * OrderItem.quantity) as total
FROM ...
WHERE o.deliveryDate > GetDate()+3
and s.storeID = s.storeID
GROUP BY s.storeName,
s.storeID,
s.peakLimit,
o.deliveryDate,
orderState
) AS e
where peakLimit < total[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-28 : 18:10:30

You cannot use an aggregate in a WHERE clause.

You will have to use it in a HAVING clause.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 18:12:16

[code]SELECT s.storeName,
s.storeID,
s.peakLimit,
o.deliveryDate,
orderState,
sum(OrderItemType.dollarValue * OrderItem.quantity) as total
FROM ...
WHERE o.deliveryDate > GetDate()+3
and s.storeID = s.storeID
GROUP BY s.storeName,
s.storeID,
s.peakLimit,
o.deliveryDate,
orderState
having min(peakLimit) < sum(OrderItemType.dollarValue * OrderItem.quantity)[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-28 : 18:17:00

I don't think you can do an aggregate on peakLimit, because you are grouping by that column.

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-28 : 20:10:20

quote:
Originally posted by Michael Valentine Jones

I don't think you can do an aggregate on peakLimit, because you are grouping by that column.

You can, but the aggregate will always be just the grouped value.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources