Combing two rows with a formulae

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

Combing two rows with a formulae

Author

Topic

bhavyam
Starting Member

4 Posts

Posted - 2011-08-10 : 02:13:18

hi
Im new to SQL programing, so please bear with me.
i hae a table with a description column and a values column

Tbl1

Des Value
order Line 5
order pieces 10
received line 20
received pieces 30

What i need to work out now is the average, so the average Ordered Pieces is Order pieces/Order lines. im not sure how to do this so any help on this matter would be great!
thanks in advance

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-08-10 : 05:18:31

Try this script,

CREATE TABLE #TEMP ([DESC] varchar(100),Value int)
INSERT INTO #TEMP VALUES('order Line',5)
INSERT INTO #TEMP VALUES('order pieces',10)
INSERT INTO #TEMP VALUES('received line',20)
INSERT INTO #TEMP VALUES('received pieces',30)

SELECT (CASE WHEN [DESC]='order Line' THEN SUM(Value) ELSE 0 end) AS Line,(CASE WHEN [DESC]='order pieces' THEN SUM(Value) ELSE 0 end) AS Pieces
INTO #TEMP1 FROM #TEMP GROUP BY [DESC]

SELECT SUM(Pieces)/SUM(Line) FROM #TEMP1

DROP TABLE #TEMP1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-10 : 07:13:02

For accuracy use

SELECT SUM(Pieces)*1.0/SUM(Line) FROM #TEMP1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 09:20:55

quote:
Originally posted by sql-programmers

Try this script,

CREATE TABLE #TEMP ([DESC] varchar(100),Value int)
INSERT INTO #TEMP VALUES('order Line',5)
INSERT INTO #TEMP VALUES('order pieces',10)
INSERT INTO #TEMP VALUES('received line',20)
INSERT INTO #TEMP VALUES('received pieces',30)

SELECT (CASE WHEN [DESC]='order Line' THEN SUM(Value) ELSE 0 end) AS Line,(CASE WHEN [DESC]='order pieces' THEN SUM(Value) ELSE 0 end) AS Pieces
INTO #TEMP1 FROM #TEMP GROUP BY [DESC]

SELECT SUM(Pieces)/SUM(Line) FROM #TEMP1

DROP TABLE #TEMP1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

see
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-10 : 09:43:23

Read this article and re-do your data encoding scheme inot a hierarchical scheme.

http://www.sqlservercentral.com/articles/Stairway+Series/Data+Encoding+schemes/72663/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources