Create temporary column that shows results

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

Create temporary column that shows results

Author

Topic

Paul Skinner
Starting Member

9 Posts

Posted - 2007-12-10 : 17:50:48

*Groan* Sorry. One more.

ERD above is what my database looks like (ignore it's in Access, database is in SQL 2005)

I have this code:

SELECT orderID, orderAmount = SUM(customerID)/customerID FROM orders
GROUP BY orderID
-- When Sum of the customer ID (and then) divided by the customer ID > 1
HAVING orderAmount > 1

which doesn't work because I never did find out how you make a column in the results to output your maths in.
orderAmount doesn't exist as a column in the database, but for this query it should show only those customers who have ordered more than once with the company.

Thanks again for any replies.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 17:54:43

SELECT orderID, COUNT(*) as orderAmount FROM orders
GROUP BY orderID
-- When Sum of the customer ID (and then) divided by the customer ID > 1
HAVING COUNT(*) > 1

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 17:57:44

The above query is equal to the one you posted originally.
Will give same result, but is not correct.

This is what you want.

SELECT CustomerID FROM Orders GROUP BY CustomerID GROUP BY CustomerID HAVING COUNT(*) > 1

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

Paul Skinner
Starting Member

9 Posts

Posted - 2007-12-10 : 18:15:37

quote:
Originally posted by Peso

The above query is equal to the one you posted originally.
Will give same result, but is not correct.

This is what you want.

SELECT CustomerID FROM Orders GROUP BY CustomerID GROUP BY CustomerID HAVING COUNT(*) > 1

There's an extra GROUP BY CustomerID in there that isn't needed

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 18:19:06

You noticed the emphasis on GROUP BY CustomerID?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources