Sql query - 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)

Sql query

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2010-01-13 : 18:49:57

Hello I'm new to SQL and can't figure out what I'm doing wrong with this query.

SELECT Artist, ItemName, Format, SUM(Quantity) AS Total, OrderDate, RetailPrice
FROM Orders
WHERE (Complete = 1)

I would like it to do a sum of the total sold, but I get this error:

Error Message: Column "orders.Artist" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Artist field is a ntext. How can I get it to do a sum with a ntext field?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 19:05:00

You need to GROUP BY the remaining fields.

SELECT Artist, ItemName, Format, OrderDate, RetailPrice,SUM(Quantity) AS Total
FROM Orders
WHERE (Complete = 1) 
GROUP BY Artist, ItemName, Format, OrderDate, RetailPrice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 00:22:52

whts the sum you're trying to retrieve? sum of quantity for an item? also will all other fields (Format, OrderDate, RetailPrice) exists only once for an item?

taunt
Posting Yak Master

128 Posts

Posted - 2010-01-14 : 11:24:32

quote:
Originally posted by vijayisonly

You need to GROUP BY the remaining fields.
SELECT Artist, ItemName, Format, OrderDate, RetailPrice,SUM(Quantity) AS Total
FROM Orders
WHERE (Complete = 1)
GROUP BY Artist, ItemName, Format, OrderDate, RetailPrice

Artist, ItemName are ntext so you can't group by them. To sort by artist I have to convert:

SELECT Artist, ItemName, MediaType, Complete, Quantity, OrderDate, RetailPrice
FROM Orders
WHERE (Complete = 1)
ORDER BY CONVERT(varchar(30), Artist)

But when I throw in the sum. I get the error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 11:26:00

quote:
Originally posted by taunt

quote:
Originally posted by vijayisonly

You need to GROUP BY the remaining fields.
SELECT Artist, ItemName, Format, OrderDate, RetailPrice,SUM(Quantity) AS Total
FROM Orders
WHERE (Complete = 1)
GROUP BY Artist, ItemName, Format, OrderDate, RetailPrice

Artist, ItemName are ntext so you can't group by them. To sort by artist I have to convert:

SELECT Artist, ItemName, MediaType, Complete, Quantity, OrderDate, RetailPrice
FROM Orders
WHERE (Complete = 1)
ORDER BY CONVERT(varchar(30), Artist)

But when I throw in the sum. I get the error

dont use text,ntext etc they're deprecated from sql 2005 onwards. use varchar(max),nvarchar(max) instead

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-14 : 11:27:21

Also please provide some sample data and expected output. Its not very clear what you are trygin to do actually...

taunt
Posting Yak Master

128 Posts

Posted - 2010-01-14 : 11:27:26

quote:
Originally posted by visakh16

whts the sum you're trying to retrieve? sum of quantity for an item? also will all other fields (Format, OrderDate, RetailPrice) exists only once for an item?

I'm trying do report of what sold, and I would like it to add quantities of items that are the same in the report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 11:29:37

do you mean this?


SELECT Artist, ItemName, Format, SUM(Quantity) OVER (PARTITION BY ItemName) AS Total, OrderDate, RetailPrice
FROM Orders
WHERE (Complete = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources