SELECT question - 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

General SQL Server Forums

New to SQL Server Programming

SELECT question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-11-26 : 05:10:04

I have a table containing the following columns:

boxes quantity item price
------ --------- ----- -----
2 12 shirts 2.90
1 24 pants 3.10
10 24 shirts 2.85
3 12 caps 1.90
4 36 socks 1.00
12 36 shirts 2.90
8 24 shirts 2.90

I need to select, say, shirts and display the sum of
boxes and quantity for each price level of shirts giving also the box count(s) (quantity)
that they are available in like so:

Shirts

Price Box count Stock level

2.90 12/24/36 648
2.85 24 240

How can I do this please?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-26 : 05:15:00

[code]select price, box = fn_concat_box(item, price), StockLevel = sum(box * quantity)
from table
where item = 'shirt'
group by price[/code]

see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254 or http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx
on how to write the concat function

KH
[spoiler]Time is always against us[/spoiler]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-11-26 : 06:22:40

Many thanks khtan. I shall now attempt my first function.
Hold tight everyone, here we go!

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-05 : 11:59:56

I'm making a mess of this right now.

My select statement is:

SELECT retail, supplier, total, boxes = dbo.fn_concat_boxes(item, price) from flexing_stock_transactions WHERE (item = 'shirt' AND week = 35 AND firm_or_commission = 'C') group by retail, supplier, total ORDER BY retail DESC

I created a function so:

CREATE FUNCTION fn_concat_boxes(@item varchar, @price int)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @strOutput VARCHAR(200)

SET @strOutput = ''

SELECT @strOutput = @strOutput + '/' + [boxes]
FROM flexing_stock_transactions
WHERE [item] = @item AND [price] = @price
ORDER BY boxes DESC

RETURN STUFF(@strOutput, 1, 1, '')

END

but I get the error:

Msg 8120, Level 16, State 1, Line 1
Column 'flexing_stock_transactions.item' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'flexing_stock_transactions.price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Where am I going wrong please?

Newbies eh? :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources